如何在SQL Server中逆透视GA4的event_params(JSON数据)

针对你用Microsoft内置连接器从BigQuery导出GA4数据后，event_params字段的嵌套JSON结构解析需求，以下是基于OPENJSON和CROSS APPLY的完整解决方案，可将参数逆透视为单独列：

核心思路 #

1. 逐层解析嵌套JSON：先拆解外层的参数数组，再提取每个参数的名称和对应有效值
2. 统一值提取逻辑：GA4参数值会根据类型存放在f数组的不同位置，用COALESCE取非空值
3. 透视转换：将行式的参数键值对转为列式结构

完整SQL代码 #

SELECT 
    base.event_date,
    base.event_timestamp,
    -- 按需添加需要的参数列
    [page_location],
    [ga_session_number],
    [page_title],
    [engaged_session_event],
    [ga_session_id],
    [ignore_referrer],
    [page_referrer],
    [session_engaged]
FROM (
    -- 基础表+第一层解析：提取event_params中的参数数组
    SELECT 
        event_date,
        event_timestamp,
        -- 解析外层v数组，获取每个参数的JSON对象
        param_json = JSON_QUERY(param.value, '$.v')
    FROM your_exported_table
    CROSS APPLY OPENJSON(event_params, '$.v') AS param
) AS base
-- 第二层解析：提取参数名和值
CROSS APPLY (
    SELECT
        param_name = JSON_VALUE(param_json, '$.f[0].v'),
        param_value = COALESCE(
            -- 字符串/布尔值通常在f[0].v
            JSON_VALUE(param_json, '$.f[1].v.f[0].v'),
            -- 数字通常在f[1].v
            JSON_VALUE(param_json, '$.f[1].v.f[1].v')
        )
) AS param_details
-- 透视转换为列
PIVOT (
    MAX(param_value)
    FOR param_name IN (
        [page_location],
        [ga_session_number],
        [page_title],
        [engaged_session_event],
        [ga_session_id],
        [ignore_referrer],
        [page_referrer],
        [session_engaged]
    )
) AS pivoted_data

说明 #

• 替换your_exported_table为你实际的导出表名
• 若有更多需要的参数，直接在PIVOT的IN子句和SELECT字段中添加即可
• COALESCE的顺序可根据你遇到的参数值位置调整，确保能取到非空的有效值
• 该方案适配SQL Server环境（支持OPENJSON函数），符合你使用Microsoft内置连接器的场景

内容的提问来源于stack exchange，提问作者Matt