Skip to content
Advertisement

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'

Advertisement

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:

enter image description here

6 People found this is helpful
Advertisement