Postgresql - Distinct array aggregation

Question

This is my current query

SELECT
project_id, 
release_id, 
array_agg(DISTINCT version_bom_id) AS version_bom_ids,
sum(case when review_status='REVIEWED' then 1 else 0 end) AS reviewed_count,
sum(case when review_status='NOT_REVIEWED' then 1 else 0 end) AS not_reviewed_count,
sum(case when severity='BLOCKER' then 1 else 0 end) AS policy_blocker_count,
sum(case when severity='CRITICAL' then 1 else 0 end) AS policy_critical_count,
sum(case when severity='UNSPECIFIED' then 1 else 0 end) AS policy_unspecified_count,
array_agg(DISTINCT ignored) as ignored_array
--problem aggregating the policy_ids
--array_agg( DISTINCT policy_ids) FILTER (where policy_ids is not null) AS policy_ids,
FROM (
 SELECT vbc.review_status, 
 vbc.version_bom_id AS version_bom_id, 
 vbc.project_id AS project_id, 
 vbc.release_id AS release_id, 
 vbc.ignored as ignored,
 min(prrd.severity) FILTER (where vbap.status = 'IN_VIOLATION') AS severity,
 array_agg(DISTINCT vbap.policy_id::UUID) FILTER (where vbap.status = 'IN_VIOLATION') AS policy_ids
 FROM
 version_bom_component vbc
 LEFT JOIN version_bom_entry vbe ON vbc.id = vbe.version_bom_component_id
 LEFT JOIN version_bom_active_policies vbap ON vbap.version_bom_entry_id = vbe.id
 LEFT JOIN policy_rule_rule_definition prrd ON prrd.id = vbap.policy_id
 GROUP BY version_bom_id, project_id, release_id, review_status, vbc.ignored
 ) t GROUP by project_id, release_id

The sub-query is giving me this result:

-[ RECORD 1 ]--+----------------------------------------------------------------------------
review_status | NOT_REVIEWED
version_bom_id | 27
project_id | 5468e2d4-e43b-4d83-b832-b654280584d0
release_id | c29d7004-5678-49a1-8e4e-56ad3831a5a3
ignored | f
severity | BLOCKER
policy_ids | {2074e6d1-3507-4fec-8a9e-f8b3c8c81caa,a9893b83-6444-400e-9ce0-f987156bbee2}
-[ RECORD 2 ]--+----------------------------------------------------------------------------
review_status | NOT_REVIEWED
version_bom_id | 28
project_id | 5468e2d4-e43b-4d83-b832-b654280584d0
release_id | c29d7004-5678-49a1-8e4e-56ad3831a5a3
ignored | f
severity | CRITICAL
policy_ids | {a9893b83-6444-400e-9ce0-f987156bbee2}
-[ RECORD 3 ]--+----------------------------------------------------------------------------
review_status | NOT_REVIEWED
version_bom_id | 28
project_id | 4859fdaf-5de6-42b7-9a6f-70354f24676c
release_id | 1eb1ee65-49fc-44a9-b673-8b85b67eaf6d
ignored | f
severity | 
policy_ids | 

I want my final output this way:

-[ RECORD 1 ]------------+----------------------------------------------------------------------------
project_id | 5468e2d4-e43b-4d83-b832-b654280584d0
release_id | c29d7004-5678-49a1-8e4e-56ad3831a5a3
version_bom_ids | {27,28}
reviewed_count | 0
not_reviewed_count | 2
policy_blocker_count | 1
policy_critical_count | 1
policy_unspecified_count | 0
policy_ids | {2074e6d1-3507-4fec-8a9e-f8b3c8c81caa,a9893b83-6444-400e-9ce0-f987156bbee2}
ignored_array | {f}
-[ RECORD 2 ]------------+----------------------------------------------------------------------------
project_id | 4859fdaf-5de6-42b7-9a6f-70354f24676c
release_id | 1eb1ee65-49fc-44a9-b673-8b85b67eaf6d
version_bom_ids | {28}
reviewed_count | 0
not_reviewed_count | 1
policy_blocker_count | 0
policy_critical_count | 0
policy_unspecified_count | 0
policy_ids | 
ignored_array | {f}

I'm having trouble aggregating the policy_ids from the sub-query. Notice that the policy_ids in the final result should be distinct. Can you anyone please help me out!

Thanks in advance

Answer 1

You could create your own aggregate to append arrays:

CREATE AGGREGATE array_append_agg(anyarray) (
 SFUNC = pg_catalog.array_cat,
 STYPE = pg_catalog.anyarray
);

Then use that aggregate function in your outer query.