Search code examples
jsonsqlitesql-updatesqlite-json1

Updating JSON in SQLite with JSON1

The SQLite JSON1 extension has some really neat capabilities. However, I have not been able to figure out how I can update or insert individual JSON attribute values.

Here is an example

CREATE TABLE keywords
(
 id INTEGER PRIMARY KEY,
 lang INTEGER NOT NULL,
 kwd TEXT NOT NULL,
 locs TEXT NOT NULL DEFAULT '{}'
);

CREATE INDEX  kwd ON keywords(lang,kwd);

I am using this table to store keyword searches and recording the locations from which the search was ininitated in the object locs. A sample entry in this database table would be like the one shown below

 id:1,lang:1,kwd:'stackoverflow',locs:'{"1":1,"2":1,"5":1}'

The location object attributes here are indices to the actual locations stored elsewhere.

Now imagine the following scenarios

  • A search for stackoverflow is initiated from location index "2". In this case I simply want to increment the value at that index so that after the operation the corresponding row reads

    id:1,lang:1,kwd:'stackoverflow',locs:'{"1":1,"2":2,"5":1}'

  • A search for stackoverflow is initiated from a previously unknown location index "7" in which case the corresponding row after the update would have to read

    id:1,lang:1,kwd:'stackoverflow',locs:'{"1":1,"2":1,"5":1,"7":1}'

It is not clear to me that this can in fact be done. I tried something along the lines of

UPDATE keywords json_set(locs,'$.2','2') WHERE kwd = 'stackoverflow';

which gave the error message error near json_set. I'd be most obliged to anyone who might be able to tell me how/whether this should/can be done.

Solution

  • I could have just deleted this question but given that the SQLite JSON1 extension appears to be relatively poorly understood I felt it would be more useful to provide an answer here for the benefit of others. What I have set out to do here is possible but the SQL syntax is rather more convoluted.

     UPDATE keywords set  locs = 
 (select json_set(json(keywords.locs),'$.**N**', 
 ifnull(
 (select json_extract(keywords.locs,'$.**N**') from keywords where id = '1'),
 0) 
 + 1) 
 from keywords where id = '1') 
 where id = '1';

    will accomplish both of the updates I have described in my original question above. Given how complicated this looks a few explanations are in order

    • The UPDATE keywords part does the actual updating, but it needs to know what to updatte
    • The SELECT json_set part is where we establish the value to be updated
    • If the relevant value does not exsit in the first place we do not want to do a + 1 on a null value so we do an IFNULL TEST
    • The WHERE id = bits ensure that we target the right row

    Having now worked with JSON1 in SQLite for a while I have a tip to share with others going down the same road. It is easy to waste your time writing extremely convoluted and hard to maintain SQL in an effort to perform in-place JSON manipulation. Consider using SQLite in memory tables - CREATE TEMP TABLE... to store intermediate results and write a sequence of SQL statements instead. This makes the code a whole lot eaiser to understand and to maintain.