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"}]

Akina · Accepted Answer · 2021-03-19 05:31:05Z

1

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

answered Mar 19, 2021 at 5:31

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

meallhour Over a year ago

Thanks. Is there a way to ensure that duplicate users are not added to the same group? For example, making sure that user4 is not added twice @Akina

Akina Over a year ago

@meallhour If you need this then use JSON_MERGE_PATCH() simply

meallhour Over a year ago

JSON_MERGE_PATCH is overwriting existing value. For example, if I add user1 first and then add user2 using JSON_MERGE_PATCH, in that case user1 is overwritten with user2

Akina Over a year ago

@meallhour If you want to ignore new values for duplicated array elements then the only visible way is in parsing input array to separate values, check them individually for the presence preserving only not existent values, then combine them into the object and merge.

meallhour Over a year ago

what will be the select query to check if any user element within @userlist is present inside table t1?

|

Collectives™ on Stack Overflow

How to append multiple entries to JSON Array in MySQL?

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related