CODEWITHSUNDEEP
mysqlsqlarraysjson
Floobinator
Floobinator

Reputation: 532

Problem with MySQL JSON Functions to Create Grouped Data

I have the following simple JSON structure in MySQL.

{"person_main":{"name_first": "Mike"}}

I want to add a GROUP of Skills for this "person_main" record so it reads:

{"person_main":{"name_first": "Mike", 
 "skills": [
  {"Name": "Learning", "Proficiency": 3}, 
  {"Name": "Teaching", "Proficiency": 2}
]}}

I cannot figure out how to insert the "Skills" data as group content into the base JSON structure using MySQL JSON functions.

While the below code doesn't work, what I want to do is:

SET @tempvar = '{"person_main":{"name_first": "Mike"}}';
SET @tempvar = JSON_SET(@tempvar, '$.person_main.skills[0].Name', 'Learning');
SET @tempvar = JSON_SET(@tempvar, '$.person_main.skills[0].Proficiency', '3');
SET @tempvar = JSON_SET(@tempvar, '$.person_main.skills[1].Name', 'Teaching');
SET @tempvar = JSON_SET(@tempvar, '$.person_main.skills[1].Proficiency', 2);

How on earth do I accomplish this with MySQL? I simply want to build a group of content within a sub field that doesn't yet exist.

Upvotes: 1

Views: 52

Answers (1)

Nick
Nick

Reputation: 147146

You can't use

JSON_SET(@tempvar, '$.person_main.skills[0].Name', 'Learning');

because the skills array doesn't exist when you try to do the JSON_SET. You can manually create all the pieces before attempting to set them but it's probably easier to use JSON_ARRAY and JSON_OBJECT:

SET @tempvar = '{"person_main":{"name_first": "Mike"}}';

SET @tempvar = JSON_INSERT(@tempvar, '$.person_main.skills', 
                               JSON_ARRAY(
                                   JSON_OBJECT('Name', 'Learning', 'Proficiency', 3),
                                   JSON_OBJECT('Name', 'Teaching', 'Proficiency', 2)
                               )
                           );

SELECT @tempvar;

Output:

{"person_main": {"skills": [{"Name": "Learning", "Proficiency": 3}, {"Name": "Teaching", "Proficiency": 2}], "name_first": "Mike"}}

Demo on dbfiddle

If you must use code in the style you have described, then you have to manually create each piece before attempting to set its values:

SET @tempvar = '{"person_main":{"name_first": "Mike"}}';
SET @tempvar = JSON_SET(@tempvar, '$.person_main.skills', JSON_ARRAY());
SET @tempvar = JSON_SET(@tempvar, '$.person_main.skills[0]', JSON_OBJECT());
SET @tempvar = JSON_SET(@tempvar, '$.person_main.skills[0].Name', 'Learning');
SET @tempvar = JSON_SET(@tempvar, '$.person_main.skills[0].Proficiency', '3');
SET @tempvar = JSON_SET(@tempvar, '$.person_main.skills[1]', JSON_OBJECT());
SET @tempvar = JSON_SET(@tempvar, '$.person_main.skills[1].Name', 'Teaching');
SET @tempvar = JSON_SET(@tempvar, '$.person_main.skills[1].Proficiency', 2);
SELECT @tempvar;

Output:

{"person_main": {"skills": [{"Name": "Learning", "Proficiency": "3"}, {"Name": "Teaching", "Proficiency": 2}], "name_first": "Mike"}}

Demo on dbfiddle

Upvotes: 2

Related Questions