How do I update a json object value by referencing itself in a sqlite table?

user_6021490 0
Viewed 385

Let's say I have a table features which has a column data that contains json objects.

CREATE TABLE features ( id INTEGER PRIMARY KEY, data json )

Now, an example data object may be:

{"A":
   {"B":
      {"coordinates":[
         {"x":1, "y":1},
         {"x":10, "y":10}
       ]
    }
}

Now I need to go through all the data in this data column and double the value of the coordinates.

I tried setting it to its own value divided by 0.5 but this seems to work for the first row and then sets all other rows to that exact same value.

update features
set data = 
(select json_set(features.data, "$.A.B.coordinates[0].x", (select json_extract(features.data, "$.A.B.coordinates[0].x")/0.5 from features))
from features);

How do I reference its own value in json_set and keep repeating for each row? What is the best way to go through each row and double the coordinates?

sqlite json
1 Answers

There is no need for a SELECT statement.
You can refer directly to the value of the column data in the same row:

UPDATE features
SET data = json_set(
             features.data, 
             "$.A.B.coordinates[0].x", json_extract(features.data, "$.A.B.coordinates[0].x") * 2
           );

Or, if you want to update all xs and ys:

UPDATE features
SET data = json_set(
             features.data, 
             "$.A.B.coordinates[0].x", json_extract(features.data, "$.A.B.coordinates[0].x") * 2,
             "$.A.B.coordinates[0].y", json_extract(features.data, "$.A.B.coordinates[0].y") * 2,
             "$.A.B.coordinates[1].x", json_extract(features.data, "$.A.B.coordinates[1].x") * 2,
             "$.A.B.coordinates[1].y", json_extract(features.data, "$.A.B.coordinates[1].y") * 2
       );

See the demo.

user_104988280
Related