如何解析SQL Server中的GA4 BigQuery RECORD列?

Question

我们使用Python脚本连接到BigQuery来下载GA4事件文件并将它们上传到SQL Server DW中。大多数列是Integer或String，它们易于使用。还有11个字段是RECORD类型的。

根据Google关于RECORDS的文档，它们是

在GoogleSQL中RECORD可以作为STRUCT类型访问。STRUCT是一个有序字段的容器。RECORD列可以具有repeat模式，它被表示为STRUCT类型的数组。此外，还有一个字段一条记录可以被重复，这被表示为STRUCT

下面是event_params数据的一个示例，它具有STRUCT类型的数组:

[{'key': 'engaged_session_event', 'value': {'string_value': None, 'int_value': 1, 'float_value': None, 'double_value': None}}
{'key': 'ga_session_number', 'value': {'string_value': None, 'int_value': 2, 'float_value': None, 'double_value': None}}
{'key': 'ga_session_id', 'value': {'string_value': None, 'int_value': 1679860220, 'float_value': None, 'double_value': None}}
{'key': 'session_engaged', 'value': {'string_value': None, 'int_value': 1, 'float_value': None, 'double_value': None}}
{'key': 'page_title', 'value': {'string_value': 'Heavy Duty Truck Parts Online, Medium Duty, Light Duty - FinditParts', 'int_value': None, 'float_value': None, 'double_value': None}}]

下面是device数据的一个示例，它是STRUCT:

{'category': 'mobile', 'mobile_brand_name': 'Apple', 'mobile_model_name': 'iPhone', 'mobile_marketing_name': None, 'mobile_os_hardware_model': None, 'operating_system': 'iOS', 'operating_system_version': 'iOS 14.8', 'vendor_id': None, 'advertising_id': None, 'language': 'en-us', 'is_limited_ad_tracking': 'No', 'time_zone_offset_seconds': None, 'browser': None, 'browser_version': None, 'web_info': {'browser': 'Safari', 'browser_version': '14.1.2'}}

是否有一个SQL函数可以用来解析STRUCTS或STRUCTS数组?这些数据是否可以以某种方式转换为JSON，以便使用内置的JSON函数来查询它?

Answer 1

使用UNNEST。例子:

SELECT
EP.key AS event_param_key,
COUNT(*) AS occurrences
FROM
-- Replace table name.
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`, UNNEST(event_params) AS EP
WHERE
-- Replace date range.
_TABLE_SUFFIX BETWEEN '20201201' AND '20201202'
GROUP BY
event_param_key
ORDER BY
event_param_key ASC;

来源此外，本文还深入介绍了如何使用UNNEST:如何使用BigQuery中的UNNEST函数来分析Analytics中的事件参数