BigQuery中拆分字符串格式JSON列返回Null，求解决方法

可能的原因及对应解决方案 #

1. 先解析JSON对象再提取字段 #

直接使用JSON_VALUE可能因字符串格式解析问题返回Null，推荐先将字符串转为JSON对象再提取字段，这种方式更稳定：

WITH cte AS (
    SELECT 
        event,
        parsed_properties.distinct_id,
        parsed_properties.time,
        parsed_properties.app_version_string,
        parsed_properties.app_build_number,
        parsed_properties.user_id
    FROM `mixpanel.event_data`
    CROSS JOIN UNNEST([PARSE_JSON(properties)]) AS parsed_properties
)
SELECT * FROM cte

2. 替换为JSON_EXTRACT_SCALAR函数 #

如果偏好路径提取的方式，尝试用JSON_EXTRACT_SCALAR替代JSON_VALUE，部分场景下兼容性更好：

WITH cte AS (
    SELECT 
        event, 
        JSON_EXTRACT_SCALAR(properties, '$.distinct_id') AS distinct_id, 
        JSON_EXTRACT_SCALAR(properties, '$.time') AS time,
        JSON_EXTRACT_SCALAR(properties, '$.app_version_string') AS app_version_string, 
        JSON_EXTRACT_SCALAR(properties, '$.app_build_number') AS app_build_number, 
        JSON_EXTRACT_SCALAR(properties, '$.user_id') AS user_id
    FROM `mixpanel.event_data`
) 
SELECT * FROM cte

3. 检查JSON结构与键名匹配 #

先执行以下查询查看properties列的实际内容，确认键名的大小写、嵌套层级是否和你的路径一致：

SELECT properties FROM `mixpanel.event_data` LIMIT 5

比如如果实际JSON是{"wrapper": {"distinct_id": "xxx"}}，那么路径需要调整为$.wrapper.distinct_id。

4. 处理格式异常的JSON #

如果JSON字符串使用单引号包裹或存在转义问题，先修正格式再解析：

WITH cte AS (
    SELECT 
        event,
        parsed_properties.distinct_id,
        parsed_properties.time,
        parsed_properties.app_version_string,
        parsed_properties.app_build_number,
        parsed_properties.user_id
    FROM `mixpanel.event_data`
    CROSS JOIN UNNEST([PARSE_JSON(REPLACE(properties, "'", '"'))]) AS parsed_properties
)
SELECT * FROM cte

内容的提问来源于stack exchange，提问作者Minh Nguyễn Nhật