Reputation: 13
I am working in Postgres V11 and have a table with a field (typeid) of int4 type.
I also have JSONB data I am getting from another table and storing into a local variable.
This data looks like
{"Types":[{"Category": "a", "TypeID": 26}, {"Category": "a", "TypeID": 27}, {"Category": "b", "TypeID": 28}, {"Category": "c", "TypeID": 30}, {"Category": "d", "TypeID": 29}]}
The query I am trying to write is
SELECT * FROM table WHERE typeid is contained in this json data
So, I only want the rows in the table returned if the typeid is listed in the json array.
I have tried different combination of this, but have yet to find something that works.
SELECT * FROM table WHERE typeid @> ANY (jsondata -> 'Types' -> 'TypeID')
The jsondata -> 'Types' seems to return just the array, but I can't seem to figure out how to specify I want to compare against the TypeID value in the object.
Postgres keeps returning
ERROR: op ANY/ALL (array) requires array on right side
So, I tried returning just the array, but it doesn't know what to compare against.
Thanks for the help!
Upvotes: 1
Views: 6817
Reputation: 44423
The ANY takes a PostgreSQL array, not an JSON array. Because JSON arrays can have heterogeneous types, they are not trivially convertible to PostgreSQL arrays. You could create a help function to do this conversion.
create function jsonb_to_intarray(jsonb,property text) returns int[] immutable parallel safe language SQL as $$
select array_agg((x->>$2)::int) from jsonb_array_elements($1) f(x)
$$;
And then:
SELECT * FROM table WHERE typeid =ANY (jsonb_to_intarray(jsondata -> 'Types', 'TypeID'));
Upvotes: 3
Reputation:
There is no single operator that you can use. You will need to unnest the array and test each element:
select t.*
from the_table t
where exists (select *
from jsonb_array_elements(t.jsondata -> 'Types') as j(element)
where j.element ? 'TypeID')
This would list rows where at least one array element contains TypeID
.
Upvotes: 1