Search code examples
sqlsqlitesqlite-json1

Using json_extract to find in all objects in JSON array

How can json_extract be used to look through all objects in an array? It works if you knew the key, but I want to look at every single object and find the one that matches.

$.features[0].properties.TMPRIV_ID

How to get this to work?

$.features[*].properties.TMPRIV_ID
Solution

  • You have this flagged with MySQL and Sqlite, so I'm going to flip a coin and give a Sqlite answer.

    Basically, you need to select from the json_each() row-valued function to iterate over each element of the array, and a where clause that filters just what you want (Which is where json_extract() comes into play):

    sqlite> SELECT value FROM
  json_each('[{"name":"cat","type":"mammal"},{"name":"parrot","type":"bird"},{"name":"dog","type":"mammal"}]')
  WHERE json_extract(value, '$.type') = 'mammal';
value                         
------------------------------
{"name":"cat","type":"mammal"}
{"name":"dog","type":"mammal"}

    If you want the results as a JSON array instead of a set of rows, use the json_group_array() aggregate function: SELECT json_group_array(value) FROM ...