Postgresql – Postgres query to select all records that have at least one matching array value from another array

arraypostgresql

Rough table structure:

id                      uuid
fields                  jsonb
created_at              timestamptz
related_content_ids     _uuid
updated_at              timestamptz

I'm trying to write a query that will find all records in this table that have at least one value from a specified array in the related_content_ids column.

Something like this works for matching all values in the array, but I just need at least one to match:

SELECT * FROM content WHERE related_content_ids @> '{fbaa1235-1069-496f-9a54-e91e13aadae8,65a109eb-ee4b-4a23-9f91-6980b5bb9340}'

Best Answer

"At least one match" can be done using the "overlaps" operator &&

select *
from content
where related_content_ids && '{fbaa1235-1069-496f-9a54-e91e13aadae8,65a109eb-ee4b-4a23-9f91-6980b5bb9340}'

Related Solutions

Postgresql – Checking for multiple identical values in a Posgresql array

Note: this will only work if you make data.id a PRIMARY KEY

SELECT
    data.id
FROM 
    data, 
    LATERAL (SELECT DISTINCT unnest(values)) no_duplicates
GROUP BY 
    data.id 
HAVING
    array_length(values, 1) > COUNT(no_duplicates)

Here's an SQL Fiddle.

This works by converting your array into a recordset/table (which I've called "no_duplicates") using unnest(), and removing duplicates using DISTINCT:

LATERAL (SELECT DISTINCT unnest(values)) no_duplicates

Then I GROUP BY the original data table's ID, and compare the length of the new, filtered recordset with the old, unfiltered table. If the original, unfiltered array is bigger, then we removed duplicates so we should select that row:

array_length(values, 1) > COUNT(no_duplicates)

PostgreSQL, finding elements by value in numeric JSON arrays

You're using the wrong operator, you want @> (added in 9.4). ?& only operates over JSON objects.

SELECT
  j,
  j @> '"dog"'::jsonb AS hasDog,
  j @> '["dog","waffle"]' AS hasDogAndWaffle,
  j @> '5'   AS has5,
  j @> '42'  AS has42
FROM ( VALUES
  ('[5,2,3]'::jsonb),
  ('["dog","cat","elephant","waffle"]'::jsonb)
)
  AS t(j);

                  j                   | hasdog | hasdogandwaffle | has5 | has42 
--------------------------------------+--------+-----------------+------+-------
 [5, 2, 3]                            | f      | f               | t    | f
 ["dog", "cat", "elephant", "waffle"] | t      | t               | f    | f
(2 rows)

If all you're storing is numbers, you should consider using intarray and not jsonb. It should be a lot faster.

Best Answer

Related Solutions

Postgresql – Checking for multiple identical values in a Posgresql array

PostgreSQL, finding elements by value in numeric JSON arrays

Related Question