Where clause with sub fields

SQL++
1

I have a json format like

Org [

Org1: Reliance
Org2: gasoline
      Hp gasoline

]

So the org1 and org2 enclosed in square braces not the flower braces { }

I want to select all records for gasoline org.

I ve written below query but not working.

Select * from table1 where org.org2[0] = gasoline – not working

Select * from table1 where org.org2 = gasoline – not working

Above queries are working for flower braces but not for square braces. Can anyone please help me on this??

2

@Mounika_Chowdary The example does not look like valid JSON to me. Perhaps it needs to be put into triple back-ticks so the HTML formatter does not strip some of the delimiters out.

3

@Mounika_Chowdary , Is how your data? If not correct them

 {
        "org": {
            "org1": [
                "reliance"
            ],
            "org2": [
                "gasoline",
                "Hp gasoline"
            ]
        }
 }


Will match "gasoline" any where in org.org2

SELECT t.* 
FROM mybucket AS t
WHERE ANY v IN t.org.org2 SATISFIES v = "gasoline" END;


 {
        "org":  [{
            "org1": [
                "reliance"
            ],
            "org2": [
                "gasoline",
                "Hp gasoline"
            ]
        }]
 }


Will match "gasoline" any where in org2

SELECT t.* 
FROM mybucket AS t
WHERE ANY o IN t.org SATISFIES (ANY v IN o.org2 SATISFIES v = "gasoline" END) END;