json_append array with multiple values
(1) By anonymous on 2020-06-03 00:07:35 [link] [source]
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:
{
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
:
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:
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 [link] [source]
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 [source]
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.
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]}}