postgres - compare jsonb array, ignore order?

Question

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 can Build a String from the Array. Then you can Check for equal. — Denis Kohl, Dec 25 '21 at 23:39
Closely related: [Compare postgres arrays for equality, ignoring order of elements](https://stackoverflow.com/q/12870105/1048572) — Bergi, Dec 26 '21 at 15:45

score 3 · Answer 1 · edited Dec 26 '21 at 14:47

3

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.

edited Dec 26 '21 at 14:47

answered Dec 26 '21 at 00:35

Oleksii Tambovtsev

2,666
1
3
21

score 1 · Answer 2 · answered Dec 26 '21 at 00:07

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.

postgres - compare jsonb array, ignore order?

2 Answers2