#BigQuery Dynamic array_agg grouping

dynamic_array_agg_bq.sql

-- https://stackoverflow.com/questions/64043166/aggregate-same-row-groups-into-one-row
with tt1 as (
select invoice_id, project_id, invoice_action, ll.line_item_id, ll.line_item_action
from api.invoice_lines_lifecycle ll
where true
  and ll.plan_type = 'domain'
  and ll.invoice_lifecycle = 'AccountingInvoiceFinalizedEvent'
    and ll.project_id = '6670e53320098f4e87e5dd50'
)
-- select t, (SELECT AS STRUCT * EXCEPT(line_item_id, line_item_action) FROM UNNEST([t]))
-- from tt1 t;
-- #standardSQL
SELECT ANY_VALUE(t).* EXCEPT(line_item_id, line_item_action),
  STRING_AGG(line_item_id, ', ') AS line_item_id,
  STRING_AGG(line_item_action, ', ') AS line_item_action,
--   STRING_AGG(CAST(Date AS STRING), ', ') AS Date
FROM tt1 t
GROUP BY TO_JSON_STRING((SELECT AS STRUCT * EXCEPT(line_item_id, line_item_action) FROM UNNEST([t])))
;
/*
         invoice_id                 project_id   invoice_action        line_item_id          line_item_action  
  ai_0KL32j-jaQVp4R   6670e53320098f4e87e5dd50   term_extension   li_0KL3jBh1hnWg9Z   extended_service_charge  
  ai_0GPz5Zn4LRTdII   6670e53320098f4e87e5dd50   term_extension   li_0GPznTdXhr-pND   extended_service_charge  
  ai_b3Kg6eItLYGZyF   6670e53320098f4e87e5dd50        migration   li_b3Kg8ScTPHLq50                  discount  
  ai_b3Kg6eItLYGZyF   6670e53320098f4e87e5dd50        migration   li_b3KggiHQoh6diM        new_service_charge  


         invoice_id                 project_id   invoice_action                           line_item_id               line_item_action  
  ai_b3Kg6eItLYGZyF   6670e53320098f4e87e5dd50        migration   li_b3Kg8ScTPHLq50, li_b3KggiHQoh6diM   discount, new_service_charge  
  ai_0GPz5Zn4LRTdII   6670e53320098f4e87e5dd50   term_extension                      li_0GPznTdXhr-pND        extended_service_charge  
  ai_0KL32j-jaQVp4R   6670e53320098f4e87e5dd50   term_extension                      li_0KL3jBh1hnWg9Z        extended_service_charge  

*/