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

Question

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.

score 1 · Accepted Answer · 2022-06-22 16:42:36Z

1

You can use a JSON path expression:

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

answered Jun 22, 2022 at 15:56

user330315

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

6 Comments

Not working. It doesn't raise errors but it returns the entire table.

@HuqeDato: ah sorry, should have been the @@ operator

Nope. returns nothing. I tryied also to search as text select * from mytable where datacolumn ? '{"id":"FR"}' but the same, nothing

Well, if you show a simplified input, you'll get a simplified answer ;) postgresql.org/docs/current/…

|