How to union an array when grouping?-CodePudding

I am trying to combine multiple array columns into one with distinct elements and then get a count of distinct elements. How can I do something like that in postgres?

 create temp table t as ( select 'james' as fn, array ['bond', 'milner'] as ln );

 create temp table tt as ( select 'james' as fn, array ['mcface', 'milner'] as ln );
-- expected value: james, 3

    select x.name,
           array_length()-- what to do here?
    from (
             select fn, ln
             from t
             union
             select fn, ln
             from tt
         ) as x
    group by x.name

CodePudding user response：

Why do you (want to) use arrays? That's not needed. Simply have a derived table using UNION, which eliminates duplicates, GROUP BY the name and use count().

SELECT name,
       count(*)
       FROM (SELECT name,
                    ln
                    FROM t
             UNION
             SELECT name,
                    ln
                    FROM tt) AS x
       GROUP BY name;

db<>fiddle

Side note: 9.3 is out of support for a while. Consider upgrading.

CodePudding user response：

You should unnest the arrays in the inner queries:

select x.fn,
       count(elem) 
from (
         select fn, unnest(ln) as elem
         from t
         union
         select fn, unnest(ln) as elem
         from tt
     ) as x
group by x.fn

Db<>fiddle.