Массив JSONB содержит операторы OR и AND
Рассмотрим таблицу temp (jsondata jsonb)
Postgres предоставляет способ запросить объект массива jsonb для проверки наличия с помощью
SELECT jsondata
FROM temp
WHERE (jsondata->'properties'->'home') ? 'football'
Но мы не можем использовать оператор LIKE для массива contains. Один из способов получить LIKE в массиве - использовать -
SELECT jsondata
FROM temp,jsonb_array_elements_text(temp.jsondata->'properties'->'home')
WHERE value like '%foot%'
Операция ИЛИ с LIKE может быть достигнута с помощью -
SELECT DISTINCT jsondata
FROM temp,jsonb_array_elements_text(temp.jsondata->'properties'->'home')
WHERE value like '%foot%' OR value like 'stad%'
Но я не могу выполнить операцию И с оператором LIKE в массиве JSONB, содержащем.
json выглядит так - {"properties": {"home": ["football", "stadium", "16"]}} Я хочу выполнить и выполнить такую операцию, как -% foot% и% stad% в домашнем массиве
Но тогда у вас нет значения один с обоими, которые вы хотите, когда используете AND
Верный. Вот почему я не могу выполнить эту операцию. Я только что попробовал этот вариант. Но если есть лучший способ без использования ценности, то это тоже будет лучше. Все, чего я хочу добиться, - это выполнить операцию LIKE с массивом JSONB с операциями OR и AND.
Ответы 3
У меня есть следующее, но это было немного неудобно. Возможно, есть способ получше, но я думаю, он работает.
Идея состоит в том, чтобы найти соответствующие записи массива JSON, а затем собрать результаты. В условии соединения мы проверяем, что массив «совпадений» имеет ожидаемое количество записей.
CREATE TABLE temp (jsondata jsonb);
INSERT INTO temp VALUES ('{"properties":{"home":["football","stadium",16]}}');
SELECT jsondata FROM temp t
INNER JOIN LATERAL (
SELECT array_agg(value) AS matches
FROM jsonb_array_elements_text(t.jsondata->'properties'->'home')
WHERE value LIKE '%foo%' OR value LIKE '%sta%'
LIMIT 1
) l ON array_length(matches, 1) = 2;
jsondata
-------------------------------------------------------
{"properties": {"home": ["football", "stadium", 16]}}
(1 row)
После распаковки массива с помощью jsonb_array_elements() вы можете проверить соответствие values одному из условий и просуммировать их по группам по исходным строкам, например:
drop table if exists temp;
create table temp(id serial primary key, jsondata jsonb);
insert into temp (jsondata) values
('{"properties":{"home":["football","stadium","16"]}}'),
('{"properties":{"home":["football","player","16"]}}'),
('{"properties":{"home":["soccer","stadium","16"]}}');
select jsondata
from temp
cross join jsonb_array_elements_text(temp.jsondata->'properties'->'home')
group by jsondata
-- or better:
-- group by id
having sum((value like '%foot%' or value like 'stad%')::int) = 2
jsondata
---------------------------------------------------------
{"properties": {"home": ["football", "stadium", "16"]}}
(1 row)
Обновлять. Вышеупомянутый запрос может быть дорогостоящим с большим набором данных. Есть упрощенное, но более быстрое решение. Вы можете преобразовать массив в текст и применить к нему like, например:
select jsondata
from temp
where jsondata->'properties'->>'home' like all('{%foot%, %stad%}');
jsondata
---------------------------------------------------------
{"properties": {"home": ["football", "stadium", "16"]}}
(1 row)
Это я уже пробовал. Но это включает в себя объединение и группировку, с помощью которых выполняются тяжелые операции. Итак, я бы предпочел что-то, что не связано с этой операцией. просто используя оператор LIKE и AND.
демонстрация: db <> рабочий пример
Я бы преобразовал массив в текст. Затем вы можете искать ключевые слова с помощью любого строкового оператора.
Недостаток: поскольку это был массив, текст содержит такие символы, как фигурные скобки и запятые. Так что не так просто искать ключевое слово с определенным началом (ABC%): всегда нужно искать как %ABC%
SELECT jsondata
FROM (
SELECT
jsondata,
jsondata->'properties'->>'home' as a
FROM
temp
)s
WHERE
a LIKE '%stad%' AND a LIKE '%foot%'
Вы уверены, что у вас есть какое-либо значение "home", которое содержит "foot" и начинается с "stad"?