Redshift SQL array intersect

Question

Is there an array intersect function in Redshift, or a good way it can be done?

For example in Snowflake it is:

    SELECT array_intersection(ARRAY_CONSTRUCT('A', 'B'), ARRAY_CONSTRUCT('B', 'C'));

    Output: ["B"]

The documentation doesn't seem to have this function or anything I can find remotely useful.

I am avoiding unnesting the arrays to join if I don't have to.

Any ideas?

Answer 1

I don't think it has any built-ins like that as far as I'm aware. You mentioned not wanting to unnest, but I think that is going to be your best (and only) option on Redshift:

create temp table rs_arrays(
    id int,
    val super
);

insert into rs_arrays (id, val)
select 1, array('a', 'b')
union
select 2, array('b', 'c');

with cte AS (
    SELECT rsa.*,
       v
FROM rs_arrays rsa, rsa.val v
)

select distinct c1.v
from cte c1
inner join cte c2
    ON c1.v = c2.v
    and c1.id != c2.id