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]
