How do I iterate over an array in a nested json object in sqlite?

Question

Assume I have a sqlite 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":10},
         {"x":10, "y":2},
         {"x":12, "y":12}
       ]
    }
}

Now the number of json objects in the coordinates array can vary from row to row. Some documents can have 3 coordinates (example above) while others may have 5 or more coordinates.

For each row or document, I want to be able to iterate over just the x values and find the minimum, same for y values. So the results for the example would be 1 for x and 2 for y.

I can get all the json objects inside the array using json_each but I can't extract x for just one single row. I tried:

select value from features, json_each(json_extract(features.data, '$.A.B.coordinates'));

However, this seems to return "all" coordinate json objects for all rows. How do I go about iterating the array for one document and extract values from it so I can then select a minimum or maximum for one document?

Answer 1

Use json_extract() again after json_each(json_extract()) to extract each x and y and aggregate:

SELECT f.id, 
       MIN(json_extract(value, '$.x')) x, 
       MIN(json_extract(value, '$.y')) y
FROM features f, json_each(json_extract(f.data, '$.A.B.coordinates'))
GROUP BY f.id

See the demo.