jsonsqlitesqlite-json1

Sqlite: append a new element to an existing array

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')
Solution

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