CODEWITHSUNDEEP
sqljsonpostgresql
Paul Carey
Paul Carey

Reputation: 566

Query Postgres JSONB with traversal of nested key when first parent key is unknown

I'm trying to query using JSONB however I have a problem where I don't know what the first key could be.

Ideally I would be able to use a wildcard inside my query.

eg: The following works

WHERE json_data #> '{first_key,second_key}' = '"value-of-second-key"'

but I may not know the name of the first_key or want to match any of the nested sub keys. Something like.

WHERE json_data #> '{*,second_key}' = '"value-of-second-key"'

Would be ideal using a wildcard like '*'

Any advice or approaches to this would be greatly appreciated.

Upvotes: 3

Views: 1810

Answers (1)

MatheusOl
MatheusOl

Reputation: 11835

You can't use wildcard for #> operator, but you can use jsonb_each function to unnest the first level of the JSON:

SELECT *
FROM jsonb_each('{"foo": {"second_key": "xxx"}, "bar": {"other_second_key": "xxx"}, "baz": {"second_key": "yyy"}}') AS e(key, value)
WHERE e.value @> '{"second_key": "xxx"}';

Result:

 key |         value         
-----+-----------------------
 foo | {"second_key": "xxx"}
(1 row)

If you just want to search for the row matching it though (and not the exact json element, as above) you can use EXISTS:

SELECT ...
FROM the_table t
WHERE EXISTS(
    SELECT 1
    FROM jsonb_each(t.the_jsonb_column) AS e(key, value)
    WHERE e.value @> '{"second_key": "xxx"}'
)

Logically, this approach works fine, but be warned that it can't get advantage of an index as would e.value @> '{"foo": {"second_key": "xxx"}}', so if performance is really a matter, you may want to rethink your schema.

Upvotes: 3

Related Questions