PostgreSQL jsonb - searching string through array elementrs

Question

Case 1 (works fine) : my db entry is a simple object

employee_id | data (jsonb)
4           | {"name":"john"}

SELECT * 
FROM employee
WHERE data ? 'john';

It will correctly find the whole entry

Case 2 : my db entry is an array of elements

employee_id | data
4           | [{"name":"john"},{"city":"rio"}]

I can't manage to use a jsonb function to find rows that match 'john' anywhere. At best I can use WHERE data->0 ? 'john' but this is not supposed to depend on the index of the array entry

Thx!

Your "working" example doesn't because 'john' is not the key. — Laurenz Albe
– Laurenz Albe, Commented Nov 29, 2019 at 12:48

Laurenz Albe · Accepted Answer · 2019-11-29 12:48:28Z

1

You can use the jsonb “contains” operator @> like this:

... WHERE data @> '[{"name": "john"}]'

That will find all rows where data has an array on the top level that contains this entry.

answered Nov 29, 2019 at 12:48

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL jsonb - searching string through array elementrs

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related