CODEWITHSUNDEEP
postgresql
user17319252
user17319252

Reputation:

postgres - compare jsonb array, ignore order?

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

Upvotes: 4

Views: 2288

Answers (2)

Oleksii Tambovtsev
Oleksii Tambovtsev

Reputation: 2834

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.

Upvotes: 3

Yevhenii Kosmak
Yevhenii Kosmak

Reputation: 3860

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:

enter image description here

I believe, the query is quite self-descriptive. A DB fiddle.

Upvotes: 2

Related Questions