BigQuery：将嵌套子JSON提取为行数据

问题场景 #

BigQuery某字段存储了动态数量的任务JSON结构，每个任务包含viewedDate（查看日期）和completedDate（完成日期），日期以秒和纳秒的格式存储。需要提取所有任务的这两个日期，但之前的尝试只能返回第一个任务的completedDate。

示例数据 #

{
    "task_1a232445": {
        "completedDate": {
            "_seconds": 1670371200,
            "_nanoseconds": 516000000
        },
        "viewedDate": {
            "_seconds": 1666652400,
            "_nanoseconds": 667000000
        }
    },
    "task_1a233445": {
        "completedDate": {
            "_seconds": 1670198400,
            "_nanoseconds": 450000000
        },
        "viewedDate": {
            "_seconds": 1674000000,
            "_nanoseconds": 687000000
        }
    }
}

解决方案 #

利用BigQuery的JSON_QUERY_ARRAY函数将JSON对象转换为键值对数组，再通过UNNEST展开所有任务，最后提取并转换日期字段：

WITH sample_data AS (
  SELECT '''{
    "task_1a232445": {
        "completedDate": {
            "_seconds": 1670371200,
            "_nanoseconds": 516000000
        },
        "viewedDate": {
            "_seconds": 1666652400,
            "_nanoseconds": 667000000
        }
    },
    "task_1a233445": {
        "completedDate": {
            "_seconds": 1670198400,
            "_nanoseconds": 450000000
        },
        "viewedDate": {
            "_seconds": 1674000000,
            "_nanoseconds": 687000000
        }
    }
}''' AS tasks_json
)
SELECT
  JSON_EXTRACT_SCALAR(task, '$."key"') AS task_id,
  -- 转换完成日期为Timestamp格式
  TIMESTAMP_SECONDS(SAFE_CAST(JSON_EXTRACT_SCALAR(task, '$."value".completedDate._seconds') AS INT64)) + 
  INTERVAL SAFE_CAST(JSON_EXTRACT_SCALAR(task, '$."value".completedDate._nanoseconds') AS INT64) NANOSECOND AS completed_date,
  -- 转换查看日期为Timestamp格式
  TIMESTAMP_SECONDS(SAFE_CAST(JSON_EXTRACT_SCALAR(task, '$."value".viewedDate._seconds') AS INT64)) + 
  INTERVAL SAFE_CAST(JSON_EXTRACT_SCALAR(task, '$."value".viewedDate._nanoseconds') AS INT64) NANOSECOND AS viewed_date
FROM sample_data,
UNNEST(JSON_QUERY_ARRAY(tasks_json, '$.keyvalue()')) AS task

关键说明 #

1. JSON_QUERY_ARRAY(tasks_json, '$.keyvalue()')：将JSON对象转换为包含键值对的数组，每个元素结构为{"key": "任务ID", "value": "任务详情"}。
2. UNNEST：展开数组，将每个任务转为单独的行。
3. 日期转换：通过TIMESTAMP_SECONDS将秒数转为时间戳，再加上纳秒偏移量，得到精确的日期时间。

之前仅返回第一个日期的原因是未对JSON对象进行数组转换和展开，直接提取只会获取第一个键值对的内容。

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