JSON filter (like SQL SELECT)

Question

I have a json file/stream, i like to be able to make select SQL style

so here is the file

the file contain all the data i have, I'll like to be able to show, let said :

all the : odeu_nom and odeu_desc that is : categorie=Feuilles

if you can do that with PHP and json (eval) fine... tell me how...

on the other part in sql i will do : SELECT * from $json where categorie=Feuilles

p.s. i have found : jsonpath that is a xpath for json... maybe another option ?

p.s. #2... with some research, i have found anoter option, the json is the same as a array, maybe I can filter the array and just return the one i need ?... how do i do that ?

Answer 1

It makes more sense to try and stick with XPath-style selectors (like jsonpath), rather than using SQL, even if you are more familiar with SQL.

The advantage of the "path" is that it is more readily expressive of the hierarchical structure implicit to XML/JSON, as opposed to SQL which requires using various joins to help it "get out of its rectangular/tabular prison".

Although I never used jsonpath, by reading its summary page, I believe that the following should produce all the odeu_nom for objects which catagorie is 'Feuilles' (given the json input referred in the question).

$.Liste_des_odeurs[?(@.categorie = 'Feuilles'].odeu_nom

which correspond to the following XPath

/Liste_des_odeurs[categorie='Feuilles']/odeu_nom

Et voila... BTW, 'Jazz is not dead, it just smells funny' (F Zappa)