Get matching elements from an array comparison

Question

I'm attempting to get matching elements regarding two arrays. I need to select them, so it essentially goes as follows

Contstraints:

Using Postgres 12, with no plugins.
String arrays, not int.

SQL I've come up with:

SELECT
    elb.1,
    elb.2,
    esp.1,
    esp.2
FROM
    schema.table1 as esp
    JOIN schema.table2 elb ON (elb.1 = esp.1)
WHERE
    (esp.3 && ('{DHE:false, DHE:true, CAM:true}'::text[]))

Let's assume esp.3 ==

'{
    BHE:false,
    DHE:true,
    CMD:true,
}'

What is the cleanest way to get that matching value DHE:true from these two arrays? I can't really alter the format of what i have here by much, so no functions etc.

I was really hoping to have something like:

SELECT
    elb.1,
    elb.2,
    esp.1,
    esp.2,
    diff
FROM
    schema.table1 as esp
    JOIN schema.table2 elb ON (elb.1 = esp.1)
WHERE
    select((esp.3 in any('{DHE:false, DHE:true, CAM:true}'::text[]))) as diff;

But i couldn't find any solution or variation on that. Any help is greatly appreciated!

Write a function `array_intersect` that uses `unnest` and joins. — Laurenz Albe, Jul 16 '20 at 19:37

score 0 · Answer 1 · answered Jul 16 '20 at 19:01

any only returns true or false so it can stop looking once it matches.

You can use the technique from this answer to get the intersection of two text arrays.

SELECT
    elb.1,
    elb.2,
    esp.1,
    esp.2,
    ARRAY(
      SELECT *
      FROM UNNEST( esp.3 )
      WHERE UNNEST = ANY('{DHE:false, DHE:true, CAM:true}'::text[])
    ) as diff
FROM
    schema.table1 as esp
    JOIN schema.table2 elb ON (elb.1 = esp.1)
WHERE
    (esp.3 && ('{DHE:false, DHE:true, CAM:true}'::text[]))

You can DRY it up with a CTE to first take the diff, then check it.

with esp as (
  select
    1,
    2,
    ARRAY(
      SELECT *
      FROM UNNEST( 3 )
      WHERE UNNEST = ANY('{DHE:false, DHE:true, CAM:true}'::text[])
    ) as diff
  from schema.table1
)
select
  elb.1,
  elb.2,
  esp.1,
  esp.2,
  esp.diff
from
  schema.table2 elb
  join esp on (elb.1 = esp.1)
where cardinality(esp.diff) != 0;

Get matching elements from an array comparison

1 Answers1