How to append multiple entries to JSON Array in MySQL?

Question

I am using MySQL 8 and trying to update JSON data type in a mysql table

My table t1 looks as below:

# id    group   names   
1100000 group1  [{"name": "name1", "type": "user"}, {"name": "name2", "type": "user"}, {"name": "techDept", "type": "dept"}]

The JSON format has two types - user and dept

Now, I have a list of users userlist as below:

SET @userlist = '["user4", "user5"]';

I want to append @userlist to the JSON Array:

UPDATE t1 SET names = JSON_ARRAY_APPEND(names, '$', JSON_OBJECT('name', @userlist, 'type', 'user'))
WHERE `group` = 'group1';

The query is working but it is incorrectly adding data as below:

[{"name": "name1", "type": "user"}, 
{"name": "name2", "type": "user"},
{"name": "["user4", "user5"]", "type": "user"}
{"name": "techDept", "type": "dept"}]

Desired Output:

[{"name": "name1", "type": "user"}, 
{"name": "name2", "type": "user"},
{"name": "user4", "type": "user"},
{"name": "user5", "type": "user"},
{"name": "techDept", "type": "dept"}]

user_10138734 · Answer

UPDATE t1
JOIN ( SELECT JSON_ARRAYAGG(JSON_OBJECT('name', username, 'type', 'user')) userlist
       FROM JSON_TABLE (@userlist,
                        '$[*]' COLUMNS (username VARCHAR(255) PATH '$')) jsontable ) jsontable
SET t1.names = JSON_MERGE_PRESERVE(t1.names, jsontable.userlist)
WHERE t1.`group` = 'group1';

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=da25f6643c623d197a220931489864e2

How to append multiple entries to JSON Array in MySQL?

1 Answers