CODEWITHSUNDEEP
postgresqljsonb
lgean
lgean

Reputation: 295

PostgreSQL jsonb - searching string through array elementrs

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!

Upvotes: 0

Views: 36

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247865

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.

Upvotes: 1

Related Questions