1

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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Gerzzog
  • 95
  • 7
  • 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 Jan 15 '23 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 Jan 15 '23 at 12:59
  • I've redone description. Can you check it again? – Gerzzog Jan 15 '23 at 13:27
  • What's the exact data type of your arrays? `text[]`or `int[]`? This is not a valid array literal: `{101,103,}` ... – Erwin Brandstetter Jan 15 '23 at 13:35
  • It's text[]. Because source table has type - text on column direction_id – Gerzzog Jan 16 '23 at 13:07

1 Answers1

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:

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:

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

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228