Блог

Главная - Вопросы по программированию - Postgres, где значение в массиве объектов Json

Postgres, где значение в массиве объектов Json

#arrays #json #postgresql

Вопрос:

Я работаю в Postgres V11 и у меня есть таблица с полем (typeid) типа int4.

У меня также есть данные JSONB, которые я получаю из другой таблицы и сохраняю в локальной переменной.

Эти данные выглядят следующим образом

 {"Types":[{"Category": "a", "TypeID": 26}, {"Category": "a", "TypeID": 27}, {"Category": "b", "TypeID": 28}, {"Category": "c", "TypeID": 30}, {"Category": "d", "TypeID": 29}]}

Запрос, который я пытаюсь написать, таков

 SELECT * FROM table WHERE typeid is contained in this json data

Итак, я хочу, чтобы строки в таблице возвращались только в том случае, если идентификатор типа указан в массиве json.

Я пробовал разные комбинации этого, но еще не нашел ничего, что работало бы.

 SELECT * FROM table WHERE typeid @> ANY (jsondata -> 'Types' -> 'TypeID')

Jsondata — > «Типы», похоже, возвращает только массив, но я, похоже, не могу понять, как указать, что я хочу сравнить со значением TypeID в объекте.

Postgres продолжает возвращаться

ОШИБКА: для ЛЮБОГО/ВСЕГО (массива) требуется массив с правой стороны

Итак, я попытался вернуть только массив, но он не знает, с чем сравнивать.

Спасибо за помощь!

Комментарии:

1. «хранение в локальной переменной» Локально для чего?

2. Это часть более крупной функции Postgres, поэтому, в частности, данные хранятся в переменной функции jsonb и не существуют в той же таблице, в которой я выбираю. Пример данных json представляет собой упрощенную версию того, с чем я имею дело, но единственное поле, которое меня интересует в этой части, — это TypeID

Ответ №1:

ЛЮБОЙ принимает массив PostgreSQL, а не массив JSON. Поскольку массивы JSON могут иметь разнородные типы, они не являются тривиально конвертируемыми в массивы PostgreSQL. Вы можете создать функцию справки для выполнения этого преобразования.

 create function jsonb_to_intarray(jsonb,property text) returns int[] immutable parallel safe language SQL as $
  select array_agg((x->>$2)::int) from jsonb_array_elements($1) f(x)
$;

И затем:

 SELECT * FROM table WHERE typeid =ANY (jsonb_to_intarray(jsondata -> 'Types', 'TypeID'));

Ответ №2:

Нет единого оператора, которым вы могли бы воспользоваться. Вам нужно будет отменить проверку массива и протестировать каждый элемент:

 select t.*
from the_table t
where exists (select *
              from jsonb_array_elements(t.jsondata -> 'Types') as j(element)
              where j.element ? 'TypeID')

Это приведет к списку строк, в которых содержится хотя бы один элемент массива TypeID .

Метки: ,