How can I exact match jsonb arrays in postgresql (SELECT .. WHERE), whilst ignoring the order of the array?

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"].

CodePudding user response：

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.

CodePudding user response：

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.