how to query for values in a deep nested json array in Postresql?-CodePudding

I have a column of json objects (jsonb type) in Posgresql in this format:

[ {"qos1": [ {
        "country_id" : [{"id":"IT",...}, {"id":"FR",...},...]
        },...],...}
...]

So I am dealing with deep nested arrays of jsons. I need to retrieve the row containing qos1 -> country_id -> id:"FR" How to do this?

I tried different combinations such as:

SELECT *
FROM mytable 
WHERE datacolumn -> 'qos1' -> 'country_id' -> 'id' = '"FR"'

with no luck.

CodePudding user response：

You can use a JSON path expression:

select *
from the_table
where datacolumn @@ '$.qos1[*].country_id[*].id == "FR"'