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](https://stackoverflow.com/questions/19925641/check-if-a-postgres-json-array-contains-a-string) — Henry Woody, Nov 02 '22 at 03:08

score 0 · Accepted Answer · 2022-11-02T10:02:23.057

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 02 '22 at 10:02

answered Nov 02 '22 at 07:32

very elegant and simple. Works perfectly!! Thank You ! – Caldera500 Nov 02 '22 at 13:50

Postgres - Query for nested value in nested array

1 Answers1