How to store a key and value in a table from a JSON object in MySQL

Question

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.

You need to split the JSON and insert one by one programmatically on the backend. You are using PHP as backend? — Adlan Arif Zakaria, Aug 21 '19 at 07:50
@AdlanArifZakaria I'm using Java as a back-end, but I required the solution in MySQL not in Java. — B.Balamanigandan, Aug 21 '19 at 07:52
could this help: https://stackoverflow.com/questions/29137915/how-can-write-queries-in-mysql-that-can-parse-json-data-in-a-column — scgough, Aug 21 '19 at 08:27

score 0 · Answer 1 · answered Aug 21 '19 at 08:29

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.

How to store a key and value in a table from a JSON object in MySQL

1 Answers1

Linked