Querying against an inner field in postgresql jsonb column

Question

I have a jsonb column, which has a structure like this:

{
    .....other fields,
    "a" : {
        "b" : {
            "c" : "some value",
            ....other fields
        }
        .....other fields
    },
    ...other fields
}

I can have query like this, properties being column name in table MyTable:

SELECT * from
  MyTable t
WHERE t.properties @> '{"a":{"b":{"c": "some value"}}}';

But it is possible that fields before "c" are different,ie we can have:

{
        .....other fields,
        "m" : {
            "n" : {
                "c" : "some value",
                ....other fields
            }
            .....other fields
        },
        ...other fields
    }

How to modify my query for such a scenario?

Which Postgres Version are you using? – Aug 20 '20 at 05:25 — , Aug 20 '20 at 05:25
Then you can use a `jsonpath` expression. – Laurenz Albe Aug 20 '20 at 05:34 — Laurenz Albe, Aug 20 '20 at 05:34

score 2 · Accepted Answer · answered Aug 20 '20 at 05:45

2

If the nesting is always at the same level you can use a JSON/Path expression:

select *
from the_table
where properties @@ '$.*.*.c == "some value"'

answered Aug 20 '20 at 05:45

Querying against an inner field in postgresql jsonb column

1 Answers1