Как запросить массив jsonb PostgreSQL [дубликат]

Предположения:

• Postgres 9.4 или новее.
• «получить всех пользователей, которые находятся в серии 5» , как предполагается, означает: »с хотя бы одним элементом массива, который содержит {"serie": 5}. Могут быть другие .
• Работа с первым, более коротким форматом данных. Нет избыточного «данных».

Краткий ответ: используйте jsonb вместо json, и это работает:

User.where("groups @> ?", '[{"serie": 5}]')

Обратите внимание на квадратные скобки для сделайте правый операнд массивом JSON .

Почему?

Значительное недоразумение здесь: тип данных json - это не то же самое как jsonb .

Вы не объявили фактическое определение таблицы, но вы позже прокомментировали json, и в вопросе есть намек:

select json_array_elements(groups -> 'data') ->> 'serie' from users;

json_array_elements() работает только для json, для jsonb должно быть jsonb_array_elements(). Но вы пытаетесь использовать jsonb Operators @>, что невозможно для json:

groups -> 'data' @>  '?'

оператор -> возвращает тот же тип, что и левый вход. Но @> определяется только для jsonb, а не для json.

Затем вы пытаетесь использовать оператор @> для text как левый операнд. Невозможно:

groups ->> 'data' @>  ?

Существуют варианты оператора @> для разных типов (включая массивы Postgres), но не для text, а не для json.

Итак, короткий ответ: используйте jsonb вместо json. Это также позволяет использовать очень эффективные индексы:

• Индекс для поиска элемента в массиве JSON

json

Для типа данных json вы можете использовать:

SELECT *
FROM   users u
WHERE  EXISTS (
   SELECT 1
   FROM   json_array_elements(u.groups) elem 
   WHERE  elem ->> 'serie' = '5'
   );

Демос

jsonb:

SELECT *
FROM  (
   VALUES (1, jsonb '[{"serie":5, "year":3, "specialization":"Matematica", "management_id":1, "group_number":2}
                    , {"serie":5, "year":3, "specialization":"Matematica", "management_id":1, "group_number":2}]')
        , (2,       '[{"serie":7, "year":3, "specialization":"Matematica", "management_id":1, "group_number":2}
                    , {"serie":8, "year":3, "specialization":"Matematica", "management_id":1, "group_number":2}]')
        , (3,       '[{"serie":9, "year":3, "specialization":"Matematica", "management_id":1, "group_number":2}
                    , {"serie":5, "year":3, "specialization":"Matematica", "management_id":1, "group_number":2}]')
   ) users(id, groups)
WHERE  groups @> '[{"serie": 5}]';

json:

SELECT *
FROM  (
   VALUES (1, json  '[{"serie":5, "year":3, "specialization":"Matematica", "management_id":1, "group_number":2}
                    , {"serie":5, "year":3, "specialization":"Matematica", "management_id":1, "group_number":2}]')
        , (2,       '[{"serie":7, "year":3, "specialization":"Matematica", "management_id":1, "group_number":2}
                    , {"serie":8, "year":3, "specialization":"Matematica", "management_id":1, "group_number":2}]')
        , (3,       '[{"serie":9, "year":3, "specialization":"Matematica", "management_id":1, "group_number":2}
                    , {"serie":5, "year":3, "specialization":"Matematica", "management_id":1, "group_number":2}]')
   ) users(id, groups)
WHERE  EXISTS (
   SELECT 1
   FROM   json_array_elements(users.groups) elem 
   WHERE  elem ->> 'serie'  = '5'
   );