json_append array with multiple values
(1) By anonymous on 2020-06-03 00:07:35 [link]
Hello, I currently have a table with a column of json data. One element of the JSON is an array which determines the 'capacity' for something used by an application. Example: ```json { id: 3, items: { category: "foo", items: [ { "type": 1, "amount": 1 }, null, null, null, null ] } } ``` My intention is to increase that capacity (5 -> 10) by appending a series of "null" values to that array. I can append a single value with `json_insert`: ```sql SELECT json_insert((SELECT items from example), '$[' || json_array_length(items) || ']', json("null")) from example; ``` I can append an array of elements via `json("[null, null, null]"))`, but it becomes nested: ```json null, null, null, [ null, null, null ] ``` Is there a way to append several individual null items to the end of this array? Or otherwise 'flatten' the nested result. Thank you
(2) By Richard Hipp (drh) on 2020-06-03 00:19:36 in reply to 1
There is no array-concatenation function. But you can insert your NULLs one-by-one using the '#' path notation: ~~~~~ UPDATE example SET json=json_insert(json,'$.items.items[#]',NULL, '$.items.items[#]',NULL, '$.items.items[#]',NULL, '$.items.items[#]',NULL); ~~~~~ Here is another (complete, copy/paste-able) script to illustrate: ~~~~~ CREATE TABLE t1(j JSON); INSERT INTO t1(j) VALUES('{"a":[0,1,2,3],"b":[4,5,6,7]}'); UPDATE t1 SET j=json_insert(j,'$.a[#]',10,'$.a[#]',11,'$.a[#]',12,'$.a[#]',13); SELECT * FROM t1; ~~~~~ The output is: ~~~~~ {"a":[0,1,2,3,10,11,12,13],"b":[4,5,6,7]} ~~~~~
(3) By jake on 2020-06-03 03:37:12 in reply to 1 [link]
Array concatenation could be achieved like this by using a combination of the `json_each` table-valued function with the `json_group_array` aggregate function. e.g. ```sql CREATE TABLE example(items JSON); INSERT INTO example(items) VALUES('{"id":3,"items":{"category":"foo","items":[{"type":1,"amount":1},null,null,null,null]}}'); UPDATE example SET items = json_set(items, '$.items.items', (SELECT json_group_array(value) FROM (SELECT value FROM json_each(items, '$.items.items') UNION ALL SELECT value FROM json_each('[null, null, null, null, null, null, null, null, null, null]') LIMIT 10))); SELECT * FROM example; -- {"id":3,"items":{"category":"foo","items":[{"type":1,"amount":1},null,null,null,null,null,null,null,null,null]}} ```