Remove duplicates from array_agg, where elements are also arrays

Question

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?

What do you mean "there are commas in arrays" - do you have empy/null elements in your arrays? Why do you not always have exactly two elements and what do you think PostgreSQL should be doing when they differ? — Richard Huxton
– Richard Huxton, Commented Jan 15, 2023 at 12:50
Start by providing the (relevant parts of) exact table definitions showing data types and constraints as CREATE TABLE scripts; and always your version of Postgres. Do you need to preserve any particular order? Do you need to preserve NULL values from original arrays? — Erwin Brandstetter
– Erwin Brandstetter, Commented Jan 15, 2023 at 12:59
What's the exact data type of your arrays? text[]or int[]? This is not a valid array literal: {101,103,} ... — Erwin Brandstetter
– Erwin Brandstetter, Commented Jan 15, 2023 at 13:35
It's text[]. Because source table has type - text on column direction_id — Gerzzog
– Gerzzog, Commented Jan 16, 2023 at 13:07

Erwin Brandstetter · Accepted Answer · 2023-01-15 13:33:09Z

1

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

edited Jan 15, 2023 at 13:33

answered Jan 15, 2023 at 13:10

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gerzzog Over a year ago

Wow. Works great! Can you advice how to make this {,9,101} in output into {9,101} ? What should I add?

Erwin Brandstetter Over a year ago

@Gerzzog: I updated with a version that removes NULL elements

Collectives™ on Stack Overflow

Remove duplicates from array_agg, where elements are also arrays

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related