SQLite Forum

json_append array with multiple values
Login

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