Styjun

I am trying to fetch 2 columns from my data in big query. Below is my query:

SELECT user_id, ep FROM table_name limit 3

Now, event_params is a nested column. It has a key and value. Below is how the data looks like:

user_id ep.key ep.value.string_value ep.value.int_value
1 origin fcm null
2 origin fcm null
3 screen null 4
 origin auto null
 id null 97

Big query some how divides the column ep into key and values (stored in string or int). I would need data in the following format:

user_id ep.key ep.value
1 origin fcm
2 origin fcm
3 screen 4
 origin auto
 id 97

Below is for BigQuery Standard SQL

#standardSQL
SELECT user_id, 
 ARRAY(
 SELECT AS STRUCT ep.key AS key, 
 COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING)) AS value
 FROM UNNEST(ep) ep
 ) ep
FROM `project.dataset.table_name`

You can test, play with above using sample data from your question as in below example

#standardSQL
WITH `project.dataset.table_name` AS (
 SELECT 1 user_id, [STRUCT<key STRING, value STRUCT<string_value STRING, int_value INT64>>('origin', STRUCT('fcm', NULL))] ep UNION ALL
 SELECT 2 user_id, [STRUCT<key STRING, value STRUCT<string_value STRING, int_value INT64>>('origin', STRUCT('fcm', NULL))] ep UNION ALL
 SELECT 3 user_id, [STRUCT<key STRING, value STRUCT<string_value STRING, int_value INT64>>('screen', STRUCT(NULL, 4)),
 STRUCT('origin', STRUCT('auto', NULL)),
 STRUCT('id', STRUCT(NULL, 97))
 ] ep 
)
SELECT user_id, 
 ARRAY(
 SELECT AS STRUCT ep.key AS key, 
 COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING)) AS value
 FROM UNNEST(ep) ep
 ) ep
FROM `project.dataset.table_name` 

with result

Row user_id ep.key ep.value 
1 1 origin fcm 
2 2 origin fcm 
3 3 screen 4 
 origin auto 
 vid 97 

Another option can be useful in case if you need to group all rows with same user_id

#standardSQL
SELECT user_id, 
 ARRAY_AGG(STRUCT( ep.key AS key, 
 COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING)) AS value
 )) ep
FROM `project.dataset.table_name`, UNNEST(ep) ep
GROUP BY user_id 

like in below example with extra row in sample data

#standardSQL
WITH `project.dataset.table_name` AS (
 SELECT 1 user_id, [STRUCT<key STRING, value STRUCT<string_value STRING, int_value INT64>>('origin', STRUCT('fcm', NULL))] ep UNION ALL
 SELECT 1 user_id, [STRUCT<key STRING, value STRUCT<string_value STRING, int_value INT64>>('origin2', STRUCT('fcm2', NULL))] ep UNION ALL
 SELECT 2 user_id, [STRUCT<key STRING, value STRUCT<string_value STRING, int_value INT64>>('origin', STRUCT('fcm', NULL))] ep UNION ALL
 SELECT 3 user_id, [STRUCT<key STRING, value STRUCT<string_value STRING, int_value INT64>>('screen', STRUCT(NULL, 4)),
 STRUCT('origin', STRUCT('auto', NULL)),
 STRUCT('id', STRUCT(NULL, 97))
 ] ep 
)
SELECT user_id, 
 ARRAY_AGG(STRUCT( ep.key AS key, 
 COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING)) AS value
 )) ep
FROM `project.dataset.table_name`, UNNEST(ep) ep
GROUP BY user_id 

with result

Row user_id ep.key ep.value 
1 1 origin fcm 
 origin2 fcm2 
2 2 origin fcm 
3 3 screen 4 
 origin auto 
 id 97 

if you would run first option against same data you would get below result

Row user_id ep.key ep.value 
1 1 origin fcm 
2 1 origin2 fcm2 
3 2 origin fcm 
4 3 screen 4 
 origin auto 
 id 97