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.
2
Answers
You can use either the
@>
"contains structure" operatoror a jsonpath with the
@?
operator:If you’re dealing with some variability in the structure of the
jsonb
values and you don’t want to tolerate it,strict
mode proposed by @Bergi will skip the mismatched ones. The patter usingstrict
and@>
example only matches four of the examples below (1,4,5,6) – which might be preferable.If you do want to tolerate some variance, the default
lax
mode won’t mind if you’re not dealing with an array anywhere in the (sub)path you specified, or it will reach down a few array dimensions deeper on its own if it is an array, but more complex than you outlined. That way, you can catch nine examples (1,4,5,6,7,8,9,10,11) below – which might or might not be desired, but could be useful for other purposes.It’s possible to express the same rules in either mode – in
lax
you need additional filter expressions to narrow it down at the end, while instrict
you need to explicitly handle alternative cases in filters on the spot, mid-path.Here’s a playground:
Plus, additional layers of arrays: