Search code examples
postgresqlpsql

How to PSQL query for jsonb column with nested array to contain string

I have a table that has a jsonb foo with below nested structure

foo: {"bar":[{"baz":["special string"]}]}

I want to query in psql for all records whose foo.bar can contain an object with the array value at baz containing the "special string".

The farthest I've gotten is below but it's not exactly comprehensive like I need it to be:

SELECT * 
FROM table
WHERE foo->'bar'->0->'baz' = '["special string"]';

Thank you.

Solution

  • You can use either the @> "contains structure" operator

    SELECT * 
FROM table
WHERE foo @> '{"bar": [{"baz": ["special string"]}]}';

    or a jsonpath with the @? operator:

    SELECT * 
FROM table
WHERE foo @? 'strict $.bar[*] ? (@.baz[*] == "special string")';