array_agg group by multiple columns where one column can contain nulls

user_12309386 0
Viewed 32

I'm using Postgresql 11.

Current state

I have a table X that looks like this:

A    B   C
____________
PR1  L1  PL1
PR1  L1  PL2
PR1  L1  PL1
PR2  L2  PL3
PR2  L2  PL4

I want to get an array of distinct column C values, grouped by column A and then column B. And I'm able to use

SELECT A, B, ARRAY_AGG(DISTINCT C) 
FROM X
GROUP BY A, B

This gives me my desired result:

PR1, L1, {PL1, PL2}
PR2, L2, {PL3, PL4}

New requirement

I now have the possibility of null values for column B. So my data might look like:

A    B   C
____________
PR1  L1  PL1
PR1  L1  PL2
PR1      PL1
PR1      PL3

As before, I need to get an array of distinct column C values, grouped by column A and then column B.
However, I need to handle NULL column B values as below:

PR1, L1, {PL1, PL2}
PR1,   , {PL3}

Note the desired handling of rows 3 and 4 of table X where column B is null:

  1. for a given column A value (PR1), if a column C value (PL1) exists for a non-null column B value (row 1) as well as a null column B value (row 3) then in the result set it should only be included in the array for the PR1, L1 combination .
  2. for a given column A value (PR1), if a column C value (PL3) exists for a null column B (row 3) but no corresponding non-null column B value, then in the result set it should be included in the array for the PR1, null combination.
aggregate-functions postgresql sql
1 Answers

I'd preprocess Column B to change the values to empty strings first. Then I think your original code will work. Then you can filter out by the rules you described in your notes.

With temp as (
SELECT A, COALESCE(B, ''), ARRAY_AGG(DISTINCT C) 
FROM X
GROUP BY A, COALESCE(B , '')
)

Select *
From temp
Where only get rows that fit your criteria
user_85377700
Related