In a Postgres 11 database, I've got two arrays in two views which are joined to MAIN table:

vw_ideas_role_direction_mappings.user_direction_id - array (like {101,103,} or {101,103} or {101,} or {101,,,,104}) vw_ideas_role_category_mappings.user_direction_id - array like previous.

DDL of view vw_ideas_role_category_mappings:

category_id - int8
user_direction_id - array -- no constraints

DDL of view vw_ideas_role_direction_mappings:

direction_id - int8
user_direction_id - array -- no constraints

DDL table idea:

id - bigserial -- no constraints

And the following query, where I join everything:

SELECT i.id,
         array_agg(dvircm.user_direction_id || dvirdm.user_direction_id) AS directions_id
           FROM idea.ideas i
             LEFT JOIN vw_ideas_role_direction_mappings  = i.direction_id
             LEFT JOIN vw_ideas_role_category_mappings dvircm ON dvircm.category_id  = i.category_id
GROUP BY i.id

So there can be NULL in arrays and duplicates.

This query does not remove them and further more it throws error - cannot accumulate arrays of different dimensionality (maybe because there are commas ' , ' in arrays before or after digits? Because when I create array user_direction_id I use this query

array_agg(distinct vw_user_data_all_roles.direction_id))

How to get rid of the error and remove duplicates and null after combining 2 arrays?

CodePudding user response：

I think this would do what you want:

SELECT i.id, sub.directions_id
FROM   idea.ideas i
LEFT   JOIN LATERAL (
   SELECT ARRAY (
      SELECT u.id
      FROM   vw_ideas_role_direction_mappings d, unnest(d.user_direction_id) u(id)
      WHERE  d.direction_id = i.direction_id
      AND    u.id IS NOT NULL
      UNION
      SELECT u.id
      FROM   vw_ideas_role_category_mappings c, unnest(c.user_direction_id) u(id)
      WHERE  c.category_id  = i.category_id
      AND    u.id IS NOT NULL
      )
   ) sub(directions_id) ON sub.directions_id <> '{}';  -- exclude empty array?

UNION after unnesting removes duplicate array elements.
NULL values are removed.

About the ARRAY constructor:

• Why is array_agg() slower than the non-aggregate ARRAY() constructor?

Since the ARRAY constructor always returns a row (like an aggregate function), we can use CROSS JOIN. Else, we'd use LEFT JOIN .. ON true. About the LATERAL join:

• What is the difference between a LATERAL JOIN and a subquery in PostgreSQL?

If you need to preserve some original order, consider WITH ORDINALITY and ORDER BY ... See:

• PostgreSQL unnest() with element number