Search code examples
postgresqljsonbpostgresql-12

Querying against an inner field in postgresql jsonb column

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?

Solution

  • 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"'