Appending an array within JSON array-CodePudding

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

My table t1 looks as below:

# id    key     value   
1100000 key123  [{"name": "name1", "hosts": ["host123"]}]

Now, I have a list of hosts hostlist as below:

SET @hostlist = '["host343", "host345"]';

I want to append @hostlist to the hosts array within value JSON Array:

UPDATE table t1
        SET t1.value = JSON_ARRAY_APPEND('[]', '$', JSON_OBJECT('hosts', @hostlist))
        WHERE t1.key = 'key123';

Desired Output:

[{"name": "name1", "hosts": ["host123","host343", "host345"]}]

CodePudding user response：

You need to use a path to the hosts property in the existing value and append to that.

But JSON_ARRAY_APPEND() is for appending a single value to the array. You need to use JSON_MERGE_PRESERVE() to concatenate arrays. Then use JSON_REPLACE() to replace the array with this.

UPDATE t1
SET value = JSON_REPLACE(t1.value, '$[0].hosts', JSON_MERGE_PRESERVE(t1.value->'$[0].hosts', @hostlist))
WHERE t1.key = 'key123';

DEMO