-
In Google BigQuery, you’re on the right path to creating the nested structure you want for your data. The STRUCT function groups together the columns into a record, and ARRAY_AGG aggregates these records into an array. From reading your question, it seems that you want to give names to these fields inside the nested structure.
For this, you would use explicit field naming inside the STRUCT function. Here’s an updated version of your last SQL statement:
`sql
with test as (
select 123 cid, “sports” ec, “load” ea, “module” el union all
select 123,”marquee”,”load”,”home” union all
select 125,”bet_add”,”sports”,”football” union all
select 126, “bet_place”,”games”,”tennis”
)
select cid, array_agg(struct(ec as event_category,ea as event_action,el as event_label)) as events from test group by 1
`Here, the fields inside each record in your array aren’t just ordered values anymore, they are named key-value pairs: {‘event_category’: ‘sports’, ‘event_action’: ‘load’, ‘event_label’: ‘module’}, and so forth. This should help get your data closer to the GA4 format you want. If there’s anything else you need, feel free to ask!
