Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.