[SOLVED] How to insert an element into the middle of an array (json) in SQLite?

How to insert an element into the middle of an array (json) in SQLite?

I found a method json_insert in the json section of the SQLite document. But it seems to be not working in the way that I expected.

e.g. select json_insert('[3,2,1]', '$[3]', 4) as result;

The result column returns '[3,2,1,4]', which is correct.

But for select json_insert('[3,2,1]', '$[1]', 4) as result;

I am expecting something like '[3,2,4,1]' to be returned, instead of '[3,2,1]'.

Am I missing something ? I don't see there is an alternative method to json_insert.

P.S. I am playing it on https://sqlime.org/#demo.db, the SQLite version is 3.37.2.

Solution

The documentation states that json_insert() will not overwrite values ("Overwrite if already exists? - No"). That means you can't insert elements in the middle of the array.

My interpretation: The function is primarily meant to insert keys into an object, where this kind of behavior makes more sense - not changing the length of an array is a sacrifice for consistency.

You could shoehorn it into SQLite by turning the JSON array into a table, appending your element, sorting the result, and turning it all back into a JSON array:

select json_group_array(x.value) from (
    select key, value from json_each('[3,2,1]')
    union
    select 1.5, 4  -- 1.5 = after 1, before 2
    order by 1
) x

This will produce '[3,2,4,1]'.

But you can probably see that this won't scale, and even if there was a built-in function that did this for you, it wouldn't scale, either. String manipulation is slow. It might work well enough for one-offs, or when done infrequently.

In the long run, I would recommend properly normalizing your database structure instead of storing "non-blob" data in JSON blobs. Manipulating normalized data is much easier than manipulating JSON, not to mention faster by probably orders of magnitude.