mysql 存储json 对象_如何从MySQL中的JSON对象在表中存储键和值

I'm having a MySQL database tables namely ds_message and ds_params, it table ds_message contains a JSON object in each row. I would like to store the key and value of a JSON object into the table ds_params for all the records by referring the ds_message primary key id

Table: ds_message

_____________________________________________________________________________________

id key_value

_____________________________________________________________________________________

1 '{"a":"John", "b":"bat", "c":"$10"}'

2 '{"i":"Emma", "j":"Jam"}'

I'm required to insert the key_value into another table like

_________________________________________________

id message_id json_key json_value

_________________________________________________

1 1 'a' 'John'

2 1 'b' 'bat'

3 1 'c' '$10'

4 2 'i' 'Emma'

5 2 'j' 'Jam'

Table Structure:

CREATE TABLE `ds_message` (

`id` int NOT NULL,

`key_value` varchar(500) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='';

ALTER TABLE `ds_message`

ADD PRIMARY KEY (`id`);

INSERT INTO `ds_message` (`id`, `key_value`) VALUES

(1, '{"a":"John", "b":"bat", "c":"$10"}');

INSERT INTO `ds_message` (`id`, `key_value`) VALUES

(2, '{"i":"Emma", "j":"Jam"}');

CREATE TABLE `ds_params` (

`id` int NOT NULL,

`message_id` int NOT NULL,

`json_key` varchar(500) NOT NULL,

`json_value` varchar(500) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='';

ALTER TABLE `ds_params`

ADD PRIMARY KEY (`id`);

Kindly assist me how to achieve this, it may be a simple select cum insert statement or by stored procedure in an optimized way.

解决方案

Use MySql JSON functions. The following information will help you writing a stored procedure which fills the table key_value from ds_message.

To get the key array of your object, use json_keys

SELECT JSON_keys('{"foo": 1, "bar": 2}')

You can get the value of each property using a key

SELECT JSON_EXTRACT('{"foo": 1, "bar": 2}', '$.foo');

Use a nested loop to iterate the ds_message records and iterate each json object using the above methods. Insert a record for each key in each object.