SQL - selectively put items into array

Question

Is there a way to selectively choose what to put inside an array using ARRAY_AGG in BQ? I have the following table where I'm trying to put deferred cases into an array nested under the respective final case.

case_id  status    date       primary_date primary_case_id primary_status
abc123   deferred  2022-05-01 2022-08-01   ghi789          final
def456   deferred  2022-07-01 2022-08-01   ghi789          final
ghi789   final     2022-08-01 2022-08-01   ghi789          final
jkl123   deferred  2022-10-01 2022-12-01   mno456          final
mno456   final     2022-12-01 2022-12-01   mno456          final

But I get something like this when using ARRAY_AGG where the final case is also included in the array.

primary_date primary_case_id primary_status deferred_cases 
2022-08-01   ghi789          final          case_id  status    date
                                            ghi789   final     2022-08-01
                                            def456   deferred  2022-07-01
                                            abc123   deferred  2022-05-01
2022-12-01   mno456          final          case_id  status    date
                                            mno456   final     2022-12-01
                                            jkl123   deferred  2022-10-01

Is there a way to remove the final cases from the array to get the result below?

primary_date primary_case_id primary_status deferred_cases 
2022-08-01   ghi789          final          case_id  status    date
                                            def456   deferred  2022-07-01
                                            abc123   deferred  2022-05-01
2022-12-01   mno456          final          case_id  status    date
                                            jkl123   deferred  2022-10-01

user_5221944 · Answer

Consider below approach

select * replace(
  array(select as struct * 
    from t.deferred_cases
    where date != primary_date
    order by date desc
  ) as deferred_cases)
from (
  select primary_case_id,
    array_agg(struct(primary_date, primary_status) order by date desc limit 1)[offset(0)].*, 
    array_agg(struct(case_id, status, date)) deferred_cases
  from your_table
  group by primary_case_id
) t

SQL - selectively put items into array

1 Answers