I have a jsonb column called data in a table called people. The json's values are arrays. It looks like this:
{"bar":["def"],"foo":["abc","hij"]}
In the above example, this jsonb has 2 keys "bar" and "foo". Both values are arrays containing several elements. I am trying to query using several key-value pairs but the values here are single strings. I am trying to make sure the results have and only have the keys in the query and at the same time the corresponding value in the query exists in the json's arrays. For example, using
{"bar":"def", "foo":"abc"} or {"bar":"def", "foo":"hij"}
, I should be able to get the result.
But if using
{"bar":"def"} or {"foo":"abc"} or {"bar":"def", "foo":"abc", "xyz":"123"}
, I shouldn't get the result since the keys don't match exactly.
I have tried using data->'bar' @> '["def"]' AND data->'foo' @> '["abc"]' to make sure the key-value pairs in the query exist in the data jsonb, but I don't know how to filter out the rows that have more keys than in the query. I was thinking about converting all the keys in the jsonb into an array and use the keys in the query as an array to check if the array from the query contains the array from the jsonb, but couldn't really know how to do it properly. If there is any other better solution, please share your thoughts.
You can full outer join the keys of your objects, check that a key match exists, and then verify the target value exists in the array of possibilities:
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;
Usage:
select * from people where js_match(data, '{"bar":"def", "foo":"abc"}'::jsonb)
This answer uses a function to make the comparisons easier during the main selection; however, below is a pure query version:
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))