Выберите строки с jsonb, которые имеют и имеют только определенные ключи в postgresql

У меня есть столбец 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, но не мог действительно знать, как это сделать правильно . Если есть другое лучшее решение, пожалуйста, поделитесь своими мыслями.

ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
1
0
50
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Вы можете 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))

Смотрите скрипку

Спасибо, это работает! Очень умный способ исключить несовпадающие строки, чтобы получить результат

user21214911 17.02.2023 06:19

Другие вопросы по теме