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?

Evan Carroll · Accepted Answer · 2017-11-14 16:35:37Z

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.

Stack Exchange Network

PostgreSQL, finding elements by value in numeric JSON arrays

1 Answer 1

Hot Network Questions

PostgreSQL, finding elements by value in numeric JSON arrays

1 Answer 1

Related

Hot Network Questions