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

Answer 1

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.