Skip to content
Advertisement

Postgres get all elements where value matches from jsonb array

I think I am trying something simple, but after hours I just can’t get it to work. I have a table which contains a tags column, which is a jsonb array and looks like this:

I now want to write a query which returns the full object to me, when the name matches the search string. So far I came up with this:

But I get this error:

I am not sure where I should do a typecast and whether that is really the problem.

I already tried solutions from these threads, but to no avail 🙁

Advertisement

Answer

If you want each matching object on a separate row, you can use jsonb_array_elements() to unnest the array of objects, then filter:

That works in you have JSONB array (so the datatype of data is jsonb).

If, on the other hand, you have an array of json objects (so: jsonb[]), you can unnest instead:

Note that this generates two rows when two objects match in the same array. If you want just one row, you can use exists instead:

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement