Aggregating or nesting STRUCTs in Big Query SQL

I have the following table:

ClientId (Integer)	EmailCampaign (String)	CampaignDetails (STRUCT)
235	Campaign 32	SentOn: 2020-01-22, Email addresses:2, SuccessRate:1
235	Campaign 22	SentOn: 2021-02-02, Email addresses:2, SuccessRate:0.5
235	Campaign 23	SentOn: 2022-05-11, Email addresses:2, SuccessRate:0.3
235	Campaign 55	SentOn: 2020-11-03, Email addresses:2, SuccessRate:0.9
122	Campaign 22	SentOn: 2022-01-03, Email addresses:2, SuccessRate:0.9

And I would like to process the data in a way that I have a single row per customer, and it looks something like this:

ClientId (Integer)	CampaignDetails (NESTED STRUCTs)
235	EmailCampaign: Campaign 32, (SentOn: 2020-01-22, Email addresses:2, SuccessRate:1), EmailCampaign: Campaign 22, (SentOn: 2021-02-02, Email addresses:2, SuccessRate:0.5), …
122	EmailCampaign: Campaign 22, (SentOn: 2022-01-03, Email addresses:2, SuccessRate:0.9)

However, I find myself struggling to aggregate STRUCTs at the client level. Any idea on what is the best approach to solve this?

This resulting table should allow us to query it with the approach below:

SELECT * 
FROM outputTable 
WHERE ClientId = 235 AND CampaignDetails.EmailCampaign = 'Campaign 22'

Answer

Hi Jaytiger! Unfortunately this solution nests the structs in an array, and I cant query it properly to find the right email campaign using array indexes

CREATE TEMP TABLE outputTable  AS
  WITH sample AS (
  SELECT 235 AS ClientId, 'Campaign 32' AS EmailCampaign,
         STRUCT('2020-01-22' AS SentOn, '2' AS Email, 1.0 AS SuccessRate) AS CampaignDetails
   UNION ALL
  SELECT 235 AS ClientId, 'Campaign 22' AS EmailCampaign,
         STRUCT('2020-02-22' AS SentOn, '2' AS Email, 0.5 AS SuccessRate) AS CampaignDetails
   UNION ALL
  SELECT 122 AS ClientId, 'Campaign 22' AS EmailCampaign,
         STRUCT('2020-02-22' AS SentOn, '2' AS Email, 0.9 AS SuccessRate) AS CampaignDetails
  )
SELECT ClientId, ARRAY_AGG(STRUCT(EmailCampaign, CampaignDetails)) AS CampaignDetails 
  FROM sample 
 GROUP BY 1
;

-- You can query nested structures using UNNEST()
SELECT * FROM outputTable, UNNEST(CampaignDetails) cd 
  WHERE ClientId = 235 AND cd.EmailCampaign = 'Campaign 22'
;

output: