CODEWITHSUNDEEP
google-bigquery
Timo Rietveld
Timo Rietveld

Reputation: 541

Change structure of table in BigQuery

Suppose there is the following query (called 'base query' in this post):

#standardSQL
SELECT '1' AS session_id, 'product_view' AS event_name, STRUCT(['country','environment'] AS parameter_name, ['NL','production'] AS parameter_value) AS event_details
UNION ALL
SELECT '1' AS session_id, 'lister_view' AS event_name, STRUCT(['country','environment','page_type'] AS parameter_name, ['DE','staging','lister'] AS parameter_value) AS event_details
UNION ALL
SELECT '2' AS session_id, 'product_view' AS event_name, STRUCT(['country','environment'] AS parameter_name, ['DE','production'] AS parameter_value) AS event_details
UNION ALL
SELECT '2' AS session_id, 'checkout_view' AS event_name, STRUCT(['country','environment','page_type'] AS parameter_name, ['GB','production','checkout'] AS parameter_value) AS event_details
UNION ALL
SELECT '2' AS session_id, 'checkout_view' AS event_name, STRUCT(['country','environment'] AS parameter_name, ['NL','staging'] AS parameter_value) AS event_details

Now, I'd like to change the structure of this table a bit, so that the event_name column becomes a nested field per session_id. I made the following query for this:

#standardSQL
SELECT session_id, ANY_VALUE(event_name_nested) AS event_name_nested FROM (
SELECT session_id, ARRAY_AGG(event_name) OVER (PARTITION BY session_id) AS event_name_nested FROM (    
[base query, please see above]    
))
GROUP BY session_id,FORMAT('%T', event_name_nested)

However, in this redefined structure I'd also like to add the STRUCT called 'event_details' (please see base query above for details) for each event. Does anyone know how to accomplish this?

Upvotes: 0

Views: 115

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173028

Use below (with the minimal changes to your original query)

#standardSQL
SELECT session_id, 
  ANY_VALUE(events_nested) AS events_nested 
FROM (
  SELECT session_id, ARRAY_AGG(struct(event_name, event_details)) OVER (PARTITION BY session_id) AS events_nested
  FROM base_query
)
GROUP BY session_id

output is

enter image description here

Upvotes: 1

Related Questions