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?
Thanks in advance,
Craig