1

The json1 extension of sqlite3 allows querying and manipulating JSON-values (stored TEXT fields). I want to append a new element to a JSON-array residing inside some JSON-value (in a SQLite database). Unfortunately the support for JSON-arrays seems to be sub-optimal when working with dynamically sized arrays (of unknown length).

I have a trivial table that stores JSON values.

CREATE TABLE T (DATA);

There would be JSON values in that table that consist of a JSON-array as root node, e.g.:

INSERT INTO T VALUES(json_array(1,2,3,4,5));

To append to the array that is at path "$" (for simplicity), I can issue the following query:

UPDATE T SET DATA =
  (SELECT json_insert(DATA,
    '$[' || json_array_length(json_extract(DATA, '$')) || ']', 42));

This works but seems fairly horrible; I first json_extract the array, then compute it's length. This length is then concatenated into a new JSON-path expression which is then fed back into the json_insert to find the right spot. Getting the last element can be done the same way:

SELECT json_extract(DATA,
  '$[' || (json_array_length(json_extract(DATA, '$')) - 1) || ']')
FROM T;

This seems pretty cumbersome. I hoped for something like a $[-1] path but this does not seem to be supported. Can this be solved in a more elegant way in SQLite?

lambdapower
  • 1,022
  • 6
  • 12
  • 1
    I ran into this question in my own quest to resolve exactly this issue. I can only confirm your observation - the JSON array support in SQLite JSON1 is not quite there yet. It would be so much nicer if there were a `json_append` function to simply add a new element to an existing JSON array. A tip if you are still playing with SQLite JSON - using in memory tables as temporary stores makes it a whole lot easier to perform complex JSON manipulation in SQLite without resorting to highly convoluted SQL one-liners. – DroidOS May 22 '18 at 09:33

0 Answers0