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';

    fiddle

    Works with or without enclosing array brackets in this case.

    If your table is big, consider an index. See:

    To address 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 type to assume. There are multiple options:

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

    ERROR:  operator is not unique: unknown @> unknown

    Related: