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"]'::jsonb;
If ["b", "a"] contains data and data contains ["b", "a"] then ["b", "a"] is equal to data.
See Postgres docs: https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-JSON-PROCESSING.
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.
a ?& bdidn't work, trya ?& b AND b ?& a