Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE data (id integer, val text[]);
INSERT INTO data VALUES (1, '{a,b,d}');
INSERT INTO data VALUES (2, '{a,e}');
INSERT INTO data VALUES (3, '{a,b,d,e}');
INSERT INTO data VALUES (4, '{b,c,d}');

SELECT * FROM data

1 rows affected

1 rows affected

1 rows affected

1 rows affected



id
val


1
{a,b,d}

2
{a,e}

3
{a,b,d,e}

4
{b,c,d}


SELECT
    id,
    unnest(val) as array_element
FROM data


id
array_element


1
a

1
b

1
d

2
a

2
e

3
a

3
b

3
d

3
e

4
b

4
c

4
d


WITH normalized AS (
    SELECT
        id,
        unnest(val) as array_element
    FROM data
)
SELECT
    n1.id,
    n2.id,
    COUNT(*)
FROM normalized n1
JOIN normalized n2
    ON (n1.array_element = n2.array_element AND n1.id <> n2.id)
GROUP BY n1.id, n2.id
ORDER BY n1.id, n2.id


id
id
count


1
2
1

1
3
3

1
4
2

2
1
1

2
3
2

3
1
3

3
2
2

3
4
2

4
1
2

4
3
2