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.
x
$.features[0].properties.TMPRIV_IDHow to get this to work?
$.features[*].properties.TMPRIV_IDAdvertisement
Answer
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 ...