PostgreSQL, finding elements by value in numeric JSON arrays

arrayjsonpostgresql

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?

Best Answer

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.

Related Question