Using json_extract to find in all objects in JSON array

Question

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

That code is not SQL. And standard SQL does not have a json_extract function. And why did you tag two different databases? — CL.
– CL., Commented Aug 1, 2018 at 19:18

Shawn · Accepted Answer · 2018-08-02 06:18:14Z

6

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

answered Aug 2, 2018 at 6:18

Shawn

53.9k3 gold badges29 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Harry Over a year ago

Ah I see, you the json_each loops through it, nice! thanks!

Shawn Over a year ago

@mobeen Not a clue. I don't use MySQL.

Collectives™ on Stack Overflow

Using json_extract to find in all objects in JSON array

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related