Skip to content

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.


How to get this to work?




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
  WHERE json_extract(value, '$.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 ...

5 People found this is helpful