Postgres получает все элементы, где значение соответствует из массива jsonb
Я думаю, что пытаюсь сделать что-то простое, но через несколько часов я просто не могу заставить его работать. У меня есть таблица, содержащая столбец тегов, который представляет собой массив jsonb и выглядит следующим образом:
{"{\"name\": \"e-commerce\", \"weight\": 400}","{\"name\": \"management\", \"weight\": 200}","{\"name\": \"content management\", \"weight\": 200}"}
Теперь я хочу написать запрос, который возвращает мне полный объект, когда имя соответствует строке поиска. Пока я придумал это:
SELECT * FROM data
WHERE tags is not null
AND EXISTS(
SELECT FROM data ad WHERE (ad.tags -> 'name' ->> 'education')::text
);
Но я получаю эту ошибку:
[42883] ERROR: operator does not exist: jsonb[] -> unknown Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Я не уверен, где я должен сделать приведение типов и действительно ли это проблема.
Я уже пробовал решения из этих тем, но безрезультатно :-(
Я не очень хорошо знаком с массивами и объектами в postgres. Мой коллега создал таблицу, но когда я ее запрашиваю, я получаю массив объектов.
Я только что проверил определение базы данных, и это действительно jsonb: tags jsonb[] default'{}'::jsonb[]
jsonb[] почти никогда не имеет смысла. «Обычный» jsonb, который включает в себя настоящий массив JSON, — гораздо лучший выбор.
Ответы 2
Вам нужно запросить объекты в json array. Создайте эти объекты, используя jsonb_array_elements, а затем запросите json, например:
SELECT d.* FROM data d, jsonb_array_elements(d.tags) tag
WHERE tag is not null and WHERE (tag -> 'name') ? 'education'
Кроме того, примечание в исходном запросе
Этот -
WHERE (ad.tags -> 'name' ->> 'education')::text
Должно быть -
WHERE (ad.tags -> 'name') ? 'education'
Или
WHERE (ad.tags ->> 'name')::text = 'education'
РЕДАКТИРОВАТЬ 1:
Поскольку ваш тип данных не jsonb, а jsonb[], вам нужно разложить его на jsonb, используя unnest -
SELECT * FROM data d, jsonb_array_elements(unnest(d.tags)) tagObj
WHERE tagObj is not null and WHERE (tag -> 'name') ? 'education'
Или
select * from (
SELECT * FROM data d, jsonb_array_elements(unnest(d.tags)) tagobj
) a WHERE tag is not null and WHERE (tag -> 'name') ? 'education'
Первый может дать ошибку из-за того, что tagObj недоступен в этом контексте.
Второе ГДЕ в вашем ответе должно быть удалено. К сожалению, я получаю эту ошибку: [42883] ОШИБКА: функция jsonb_array_elements(jsonb[]) не существует Подсказка: Ни одна функция не соответствует заданному имени и типам аргументов. Возможно, вам потребуется добавить явное приведение типов. Позиция: 32
@Gh05d Gh05d, да, потому что ваш тип данных jsonb[] вместо jsonb, вам нужно разложить его на jsonb. Я обновил ответ.
Если вы хотите, чтобы каждый соответствующий объект находился в отдельной строке, вы можете использовать jsonb_array_elements(), чтобы удалить массив объектов, а затем отфильтровать:
select o.obj
from data d
cross join lateral jsonb_array_elements(d.tags) o(obj)
where o.obj ->> 'name' = 'education'
Это работает, если у вас есть массив JSONB (поэтому тип данных — jsonb).
Если, с другой стороны, у вас есть массив объектов json (так что: jsonb[]), вы можете unnest вместо этого:
select o.obj
from data d
cross join lateral unnest(d.tags) o(obj)
where o.obj ->> 'name' = 'education'
Обратите внимание, что это создает две строки, когда два объекта совпадают в одном и том же массиве. Если вам нужна только одна строка, вы можете вместо этого использовать exists:
select o.obj
from data d
where exists (
select 1 from unnest(d.tags) o(obj) where o.obj ->> 'name' = 'education'
)
Это действительно содержимое вашего столбца JSON? Эти экранированные двойные кавычки выглядят неправильно, кажется, что значение было сохранено как одно скалярное значение, а не как правильный объект JSON.