Postgres - Query for nested value in nested array

Question

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')

Does this answer your question? Check if a Postgres JSON array contains a string — Henry
– Henry, Commented Nov 2, 2022 at 3:08

score 0 · Accepted Answer · 2022-11-02 10:02:23Z

0

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

edited Nov 2, 2022 at 10:02

answered Nov 2, 2022 at 7:32

user330315

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

1 Comment

Caldera500 Over a year ago

very elegant and simple. Works perfectly!! Thank You !

Collectives™ on Stack Overflow

Postgres - Query for nested value in nested array

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related