Search code examples
sqlarrayspostgresqlsearchjsonb

Query with filter on value in a jsonb array

I have a column with type jsonb holding a list of IDs as plain JSON array in my PostgreSQL 9.6.6 database and I want to search this field based on any ID in the list. How to query write this query?

'[1,8,3,4,56,6]'

For example, my table is:

CREATE TABLE mytable (
    id bigint NOT NULL,
    numbers jsonb
);

And it has some values:

 id  |  numbers
-----+-------
 1   | "[1,8,3,4,56,6]"
 2   | "[1,2,7,4,24,5]"

I want something like this:

SELECT * 
FROM mytable
WHERE
id = 1
AND
numbers::json->>VALUE(56)
;

Expected result (only if the JSON array has 56 as element):

 id  |  numbers
-----+-------
 1   | "[1,8,3,4,56,6]"

Step-2 problem :

The result of this command is TRUE :

SELECT '[1,8,3,4,56,6]'::jsonb @> '56';

but already when I use 

SELECT * 
FROM   mytable
numbers::jsonb @> '[56]';

or

SELECT * 
FROM   mytable
numbers::jsonb @> '56';

or

SELECT * 
FROM   mytable
numbers::jsonb @> '[56]'::jsonb;

The result is nothing :

 id  |  numbers
-----+-------
(0 rows)

Instead of be this :

 id  |  numbers
-----+-------
 1   | "[1,8,3,4,56,6]"

I find why I get (0 rows) ! :))

because I insert jsonb value to mytable with double quotation , in fact this is correct value format (without double quotation ):

 id  |  numbers
-----+-------
 1   | [1,8,3,4,56,6]
 2   | [1,2,7,4,24,5]

now when run this command: 

SELECT * 
FROM   mytable
numbers @> '56';

The result is :

 id  |  numbers
-----+-------
 1   | [1,8,3,4,56,6]
Solution

  • Use the jsonb "contains" operator @>:

    SELECT * 
FROM   mytable
WHERE  id = 1
AND    numbers @> '[56]';

    Or 

    ...
AND    numbers @> '56';

    Works with our without enclosing array brackets in this case.

    dbfiddle here

    This can be supported with various kinds of indexes for great read performance if your table is big.

    Detailed explanation / instructions:

    Hint (addressing your comment): when testing with string literals, be sure to add an explicit cast:

    SELECT '[1,8,3,4,56,6]'::jsonb @> '56';

    If you don't, Postgres does not know which data types to assume. There are multiple options:

    SELECT '[1,8,3,4,56,6]' @> '56';

    ERROR:  operator is not unique: unknown @> unknown

    Related: