How to get all records with a specific value in a JSONB field?

Question

PostgreSQL

PostgreSQL 9.4 has a table with records. The tags field is of type JSONB .

How to get, for example, all records that have the value 1 in the tags field . Below is the code and the error that it causes.

SELECT public.mytable.* FROM public.mytable WHERE tags ? 1;

ERROR: operator does not exist: jsonb ? integer
LINE 1: SELECT public.mytable.* FROM public.mytable WHERE tags ? one;
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
********** Error **********
ERROR: operator does not exist: jsonb ? integer
SQL state: 42883
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Symbol: 52

Answer 1

You, in the tags column, contain arrays of integers (in my opinion, the column type is not chosen well and it is better to use array), and you use the string search operator. You need to use the @> operator and look for the intersection of arrays.
Example:

CREATE TABLE aaa
(
  id serial NOT NULL,
  tags jsonb,
  CONSTRAINT aaa_pk PRIMARY KEY (id)
);

-- Значения - integer
INSERT INTO aaa(tags)
    VALUES ('[1, 2, 3]'::jsonb);

INSERT INTO aaa(tags)
    VALUES ('[2, 4, 1]'::jsonb);

-- Пример для оператора ? , элемент "1" - строка
INSERT INTO aaa(tags)
    VALUES ('[9, 9, "1"]'::jsonb);

-- ------------------------------------------
select * from aaa where tags @> '[1]'::jsonb;
-- OUTPUT:
-- id | tags
-- 1  | [1, 2, 3]
-- 2  | [2, 4, 1]

-- вывести записи где есть строка "1"
select * from aaa where tags ? '1';
-- OUTPUT:
-- id | tags
-- 3  | [9, 9, "1"]

Link to documentation...