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:
{"{"name": "e-commerce", "weight": 400}","{"name": "management", "weight": 200}","{"name": "content management", "weight": 200}"}
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:
SELECT * FROM data
WHERE tags is not null
AND EXISTS(
SELECT FROM data ad WHERE (ad.tags -> 'name' ->> 'education')::text
);
But I get this error:
[42883] ERROR: operator does not exist: jsonb[] -> unknown Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
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:
select o.obj
from data d
cross join lateral jsonb_array_elements(d.tags) o(obj)
where o.obj ->> 'name' = 'education'
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:
select o.obj
from data d
cross join lateral unnest(d.tags) o(obj)
where o.obj ->> 'name' = 'education'
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:
select o.obj
from data d
where exists (
select 1 from unnest(d.tags) o(obj) where o.obj ->> 'name' = 'education'
)