Как сделать выборку по значению из JSON

#postgresql

Имеется таблица с полем типа json. Таблица заполнена значениями вида [123,854,12],
[], [12,2142141,24]
Как мне выбрать строку, в json которой содержится определенное число?
Т.е. если я ищу 12, то я должен получить две строки со значениями, перечисленными выше.
    

Ответы

Ответ 1

Если вы задействуете PostgreSQL вам разумнее воспользоваться не JSON, a типом ARRAY

CREATE TABLE tbl (
  id INTEGER,
  name INTEGER ARRAY
);
INSERT INTO tbl VALUES (1, '{123,854,12}');
INSERT INTO tbl VALUES (2, '{}');
INSERT INTO tbl VALUES (3, '{12,2142141,24}');


Тогда для поиска строк, в массиве name которых имеется значение 12 можно воспользоваться
следующим запросом

SELECT * FROM tbl WHERE 12 = ANY (name);
 id |      name
----+-----------------
  1 | {123,854,12}
  3 | {12,2142141,24}


Ответ 2

Почему json, а не jsonb? Или вообще int[], если допустимо немного изменить формат
хранения?

Есть одна неочевидная проблема. Наивное решение where fieldname ? 12 - нет такого
оператора. А where fieldname ? '12' - закономерно false, у вас в массиве не строки,
а числа.

Обходной путь - сравнивать пересечением с другим json-массивом:

WHERE fieldname @> '[12]'::jsonb


Если у вас именно json и почему-то изменить нельзя, то оператора @> для json нет
и надо разбираться, почему нельзя привести к jsonb. Если по какой-то уважительной причине
нельзя именно поле сконвертировать, то можно преобразовывать налету:

WHERE fieldname::jsonb @> '[12]'::jsonb


Если же проблема в том, что у вас старая версия postgresql и не умеет jsonb (до 9.4,
то есть) - тогда проблема. Можно написать хранимку, которая будет парсить json и сверять
значения.


Ответ 3

Сделал вот так:

select 
    id,
    param
from (select id, json_array_elements(params)::text as param from table) s 
where param = 12::text


Выводит все строки, в которых встречается 12 в json массиве. 
В ответах предложили способ лучше, воспользуюсь им.