How can I exact match jsonb arrays in postgresql (SELECT .. WHERE), whilst ignoring the order of the array?
| id | data |
|---|---|
| 1 | ["a", "b"] |
| 2 | ["b"] |
| 3 | ["a"] |
| 4 | ["c", "a", "b"] |
With the input of ["b", "a"] I expect to receive id 1, and with the input of ["a", "b", "c"] I expect to receive id 4. I have tried using the ?& operator, although it would return id 1, 2, 4 if the input was ["b"].
You should try to use contains operator @> in this way:
SELECT *
FROM your_table
WHERE '["b", "a"]'::jsonb @> data
AND data @> '["b", "a", "c"]'::jsonb;
If ["b", "a"] contains data and data contains ["b", "a"] then ["b", "a"] is equal to data.
Let's consider this schema and initial data:
CREATE TABLE test (
id serial not null primary key,
data jsonb
);
INSERT INTO test (data) VALUES
('["a", "b"]'::jsonb),
('["b"]'::jsonb),
('["a"]'::jsonb),
('["c", "a", "b"]'::jsonb);
Query would be like:
SELECT id, data FROM (
SELECT *,
ARRAY(
SELECT UNNEST(
ARRAY(SELECT jsonb_array_elements_text(data))
) AS item ORDER BY item
) AS db_array_sorted,
ARRAY(
SELECT UNNEST(
ARRAY(SELECT jsonb_array_elements_text('["b", "a"]'::jsonb))
) AS item ORDER BY item
) AS input_array_sorted
FROM test
) AS sq
WHERE db_array_sorted = input_array_sorted;
Result of execution:
I believe, the query is quite self-descriptive. A DB fiddle.
You can use the contains operator in both directions ("contains" and "is contained in"). This is only true if the arrays are equal independent of the order of the elements
select *
from the_table
where data @> '["a","b"]' --<< data contains the array
and data <@ '["a","b"]' --<< the array is contained in data
select *
from the_table
where data @> '["a","b","c"]'
and data <@ '["a","b","c"]'