如何从 BigQuery 中的 Array(Struct) 类型数据结构中获取每个键名的值

Question

我们将 Firebase 事件数据存储在 BigQuery 中。有一个列event_params是数组(STRUCT(。我们希望将这些结构键值存储为 flat(column( 存储在 mysql 表中。我们正在为每个密钥做 UNNEST，是否有可能以更智能的方式做到这一点。

SELECT event_name, user_prop.value.string_value AS org_id
            content_type.value.string_value as page_name , contentId.value.string_value as content_id,   
            platform, app_info.version AS app_version,
            event_date            
            FROM `table.name`,
            UNNEST(user_properties) AS user_prop,
            UNNEST(event_params) AS content_type,
            UNNEST(event_params) AS contentId           
            WHERE
            user_prop.key = "OrgId"
            AND
            content_type.key = 'contentType'   
            AND
            contentId.key = 'Id'

Answer 1

是的，您可以使用数组子查询来执行此操作例如

SELECT  
  platform, app_info.version AS app_version,
  event_date,
  (SELECT  value.string_value FROM UNNEST(event_params) WHERE key = 'contentType') AS page_name              
FROM `table.name`