Postgres - Query for nested value in nested array-CodePudding

I have a table called clients with a column called configs containing JSON object.

{
    "pos_link": {
        "primary_id": "000123",
        "sub_ids": ["000123", "000124", "00125", "000126"],
       
    },
    "prime_tags": {
        "tags": ["Children"]
    }
}

How do I find all entries where one of the sub_id is '00124'

select *
from clients c,
jsonb_array_elements(c.configs->'pos_link') pos_link,
jsonb_array_elements(pos_link->'sub_ids') sub_ids
where sub_id IN ('00124')

CodePudding user response：

You can use the contains operator ?

select *
from clients
where configs -> 'pos_link' -> 'sub_ids' ? '000124';

This assumes that configs is defined as jsonb (which it should be). If it's not, you need to cast it: configs::jsonb

Online example