Appending an array within JSON array

Question

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

Barmar · Accepted Answer · 2021-09-24 00:38:14Z

2

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

answered Sep 24, 2021 at 0:38

Barmar

789k57 gold badges554 silver badges669 bronze badges

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

Collectives™ on Stack Overflow

Appending an array within JSON array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related