Sqlite: append a new element to an existing array

Question

From the sqlite3 doc

select json_insert('{"a":2,"c":4}', '$.e', 99) -- → '{"a":2,"c":4,"e":99}'

But how to append a new element to an array?

select json_insert('[1,2,3]', ??, 4) -- → '[1, 2, 3, 4]'
update someTable set someArray = json_insert(someArray, ??, 'some new value') 

Answer 1

After a few trials, I finally figured out

update some_table
  set some_array = json_insert(
    some_array,
    '$[' || json_array_length(some_array) || ']',
    'new item'
  )
  where id = some_id;

Answer 2

A new notation was introduced in version 3.31.0 to directly support this functionality:

select json_insert('[1,2,3]', '$[#]', 4) -- → '[1, 2, 3, 4]'

Answer 3

There's apparently no function or easy way to do it, but we can get there by:

• Breaking up each array value into a row with json_each
• Adding rows for the values we want to add with UNION ALL
• Grouping them with a sub-query and GROUP BY
• Packing them back together with json_group_array

For example if you have a Messages table with PRIMARY KEY id and a JSON array in account, you can add an element to the account array in all rows with:

SELECT id, json_group_array(value) FROM (
    SELECT Messages.id, json_each.value
    FROM Messages, json_each(Messages.account)
    UNION ALL SELECT Messages.id, 'new_account' FROM Messages
) GROUP BY id;

Which we need to wrap once more to put in a UPDATE as SQLite does not support UPDATE + JOIN. This will add the new account to all rows:

UPDATE Messages SET account = (SELECT account FROM (
    SELECT id, json_group_array(value) as account FROM (
        SELECT Messages.id, json_each.value
        FROM Messages, json_each(Messages.account)
        UNION ALL SELECT Messages.id, 'new_account' FROM Messages
    ) GROUP BY id
) WHERE id = Messages.id);

We can simplify this if you want to update only one row like this:

UPDATE Messages SET account = (
    SELECT json_group_array(value) FROM (
        SELECT json_each.value
        FROM Messages, json_each(Messages.account)
        WHERE Messages.id = 123456789
        UNION ALL SELECT 'new_account'
    ) GROUP BY ''
) WHERE id = 123456789;