I have a table defined as:
create table dummy (jdata jsonb); I inserted the following two rows:
insert into dummy values ('["dog","cat","elephant","waffle"]'); insert into dummy values ('[1,2,3,4]'); I am trying to make use of the jsonb ?& operator which lets you ask the question "Do all of these key/element strings exist?"
An example using the string fields works:
select * from dummy where jdata ?& array['cat','dog']; jdata -------------------------------------- ["dog", "cat", "elephant", "waffle"] (1 row) But, trying to do this with an array that contains numbers does not work:
select * from dummy where jdata ?& array['1','2']; jdata ------- (0 rows) select * from dummy where jdata ?& array['1','2']; jdata ------- (0 rows) select * from dummy where jdata ?& array[1,2]; ERROR: operator does not exist: jsonb ?& integer[] LINE 1: select * from dummy where jdata ?& array[1,2]; ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. I know that the ?& operator works on text arrays, but therein lies the problem. Does anyone know how to get the json operators to work on numeric arrays?
