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
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 ...