7

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

ilyaplot
  • 3,603

3 Answers3

4

Если вы задействуете 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}
cheops
  • 19,454
  • 29
  • 48
  • 139
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 и сверять значения.

Мелкий
  • 21,843
2

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

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

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

ilyaplot
  • 3,603