Sqlite: добавить новый элемент в существующий массив

По-видимому, нет функции или простого способа сделать это, но мы можем добиться этого:

• Разбиение каждого значения массива на строку с помощью json_each
• Добавление строк для значений, которые мы хотим добавить с помощью UNION ALL
• Группировка их с помощью подзапроса и GROUP BY
• Собираем их обратно вместе с json_group_array

Например, если у вас есть таблица Messages с PRIMARY KEY id и массив JSON в account, вы можете добавить элемент в массив учетных записей во всех строках с помощью:

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;

Что нам нужно еще раз обернуть, чтобы вставить UPDATE, поскольку SQLite не поддерживает UPDATE + JOIN. Это добавит новую учетную запись во все строки:

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);

Мы можем упростить это, если вы хотите обновить только одну строку следующим образом:

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;

После нескольких попыток я наконец понял

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

В версии 3.31.0 была введена новая нотация для прямой поддержки этой функции:

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