PostgreSQL, finding elements by value in numeric JSON arrays

Question

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?

Answer 1

You're using the wrong operator, you want @> (added in 9.4). ?& only operates over JSON objects.

SELECT
 j,
 j @> '"dog"'::jsonb AS hasDog,
 j @> '["dog","waffle"]' AS hasDogAndWaffle,
 j @> '5' AS has5,
 j @> '42' AS has42
FROM ( VALUES
 ('[5,2,3]'::jsonb),
 ('["dog","cat","elephant","waffle"]'::jsonb)
)
 AS t(j);
 j | hasdog | hasdogandwaffle | has5 | has42 
--------------------------------------+--------+-----------------+------+-------
 [5, 2, 3] | f | f | t | f
 ["dog", "cat", "elephant", "waffle"] | t | t | f | f
(2 rows)

If all you're storing is numbers, you should consider using intarray and not jsonb. It should be a lot faster.