Updating JSON in SQLite with JSON1

Question

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.

Answer 1

It is not necessary to create such complicated SQL with subqueries to do this.

The SQL below would solve your needs.

UPDATE keywords 
SET locs = json_set(locs,'$.7', IFNULL(json_extract(locs, '$.7'), 0) + 1) 
WHERE kwd = 'stackoverflow';

I know this is old, but it's like the first link when searching, it deserves a better solution.