У меня есть столбец jsonb с именем data
в таблице с именем people
. Значения json представляют собой массивы. Это выглядит так:
{"bar":["def"],"foo":["abc","hij"]}
В приведенном выше примере этот jsonb имеет 2 ключа «bar» и «foo». Оба значения представляют собой массивы, содержащие несколько элементов. Я пытаюсь выполнить запрос, используя несколько пар ключ-значение, но значения здесь представляют собой отдельные строки. Я пытаюсь убедиться, что результаты имеют и имеют только ключи в запросе, и в то же время соответствующее значение в запросе существует в массивах json. Например, используя
{"bar":"def", "foo":"abc"} or {"bar":"def", "foo":"hij"}
, я должен быть в состоянии получить результат.
Но если использовать
{"bar":"def"} or {"foo":"abc"} or {"bar":"def", "foo":"abc", "xyz":"123"}
, я не должен получить результат, так как ключи точно не совпадают.
Я пытался использовать data->'bar' @> '["def"]' AND data->'foo' @> '["abc"]'
, чтобы убедиться, что пары ключ-значение в запросе существуют в данных jsonb, но я не знаю, как отфильтровать строки с большим количеством ключей, чем в запросе. Я думал о преобразовании всех ключей в jsonb в массив и использовании ключей в запросе в качестве массива, чтобы проверить, содержит ли массив из запроса массив из jsonb, но не мог действительно знать, как это сделать правильно . Если есть другое лучшее решение, пожалуйста, поделитесь своими мыслями.
Вы можете full outer join
ключи ваших объектов, проверить наличие совпадения ключей, а затем проверить, существует ли целевое значение в массиве возможностей:
create or replace function js_match(record jsonb, template jsonb) returns bool as $$
select not exists (select 1 from jsonb_each(record) t1
full outer join jsonb_each(template) t2 on t1.key = t2.key
where t1.key is null or t2.key is null or not exists
(select 1 from jsonb_array_elements(t1.value) v where v = t2.value))
$$ language sql;
Использование:
select * from people where js_match(data, '{"bar":"def", "foo":"abc"}'::jsonb)
В этом ответе используется функция, облегчающая сравнение во время основного выбора; однако ниже приведена чистая версия запроса:
select * from people p where not exists (select 1 from jsonb_each(p.data) t1
full outer join jsonb_each('{"bar":"def", "foo":"abc"}'::jsonb) t2 on t1.key = t2.key
where t1.key is null or t2.key is null or not exists
(select 1 from jsonb_array_elements(t1.value) v where v = t2.value))
Спасибо, это работает! Очень умный способ исключить несовпадающие строки, чтобы получить результат