Using JSON_ARRAY_APPEND to insert large array into JSON document

Question

I am using JSON_ARRAY_APPEND to update an array object in an existing entry in the table.

I am doing it via code and query looks as below:

UPDATE table t1
        SET t1.value = JSON_ARRAY_APPEND('[]', '$', JSON_OBJECT('desc', '${desc}', 
        'hosts', '[${hosts}]'))
        WHERE  t1.key = '${key1}

My resulting value looks as below:

[{"desc": "this is desc", "hosts": "[host1,host2, host3]"}]

Desired Output for value is:

[{"desc": "this is desc", "hosts": ["host1","host2","host3"]"}]

Vacy Vacin · Answer 1 · 2021-07-22T21:41:48.587

0

``

SELECT CONCAT('"', REPLACE('${hosts}', ',', '","'), '"');
UPDATE table t1
SET t1.value = JSON_ARRAY_APPEND('[]', '$', JSON_OBJECT('desc', '${desc}', 'hosts', '[${hosts}]'))
WHERE  t1.key = '${key1};


SET @host =  'host1,host2, host3';
UPDATE table t1
SET t1.value = JSON_ARRAY(
JSON_OBJECT(
'desc', 'this is desc', 
'hosts',
JSON_ARRAY(                           
SUBSTRING_INDEX(@host, ',', 1),
SUBSTRING_INDEX(SUBSTRING_INDEX(@host, ',', 2), ',', -1) ,
SUBSTRING_INDEX(SUBSTRING_INDEX(@host, ',', 3), ',', -1)))) ;

''

edited Jul 22 '21 at 21:41

answered Jul 22 '21 at 16:24

Vacy Vacin

1
2

Is there a way, i can do it within `JSON_ARRAY_APPEND` only without using an extra `SELECT CONCAT` statement? – meallhour Jul 22 '21 at 16:32
A way to pass to $ {hosts} data already escaped with. – Vacy Vacin Jul 22 '21 at 17:18

score 0 · Answer 2 · answered Jul 22 '21 at 17:11

You don't need to use JSON_ARRAY_APPEND() since you are creating a new array.

Use JSON_ARRAY(). This ensures the array will be formatted as valid JSON. You should try to avoid writing code that forms JSON by concatenating commas and brackets and quotes, because it's difficult to ensure this will always produce valid JSON format. It's fragile code.

Also, use query parameters instead of trying to interpolate code variables into your SQL statement.

UPDATE table t1
SET t1.value = JSON_ARRAY( 
  JSON_OBJECT(
    'desc', ?, 
    'hosts', JSON_ARRAY(?, ?, ?, ?))
WHERE t1.key = ?

You'll need one ? parameter marker for each host you want in your hosts array.

Using JSON_ARRAY_APPEND to insert large array into JSON document

2 Answers2