如何在GCP BigQuery中提取JSON数组多组taskId与reportType

场景说明 #

你的GCP BigQuery表中存在一列mReports，存储的是JSON数组格式的文本字符串，示例内容如下：

[{"taskId":"TEST_PLAN","reportId":3751693,"reportType":"TA","timestamp":1689626254539},{"taskId":"REPAIR_SELECTION","reportId":3755434,"reportType":"FRED","timestamp":1689961349740}]

需求是将数组内每组的taskId和reportType提取为单独的列，每组对应一个TaskIdN和ReportTypeN（最多可能有3组）。之前使用REGEXP_EXTRACT只能提取第一组数据，无法获取后续组的内容。

解决方案 #

不要用正则处理JSON字符串，BigQuery提供了原生的JSON处理函数，更可靠且易维护。以下是实现SQL：

WITH parsed_data AS (
  SELECT
    -- 替换为你的表中唯一标识每行的主键列（比如id）
    your_primary_key,
    -- 从每个JSON对象中提取目标字段
    JSON_VALUE(item, '$.taskId') AS task_id,
    JSON_VALUE(item, '$.reportType') AS report_type,
    -- 给每组编号（从1开始）
    offset + 1 AS group_num
  FROM
    your_table_name,
    -- 将JSON字符串转为数组并拆分，保留元素位置偏移量
    UNNEST(JSON_EXTRACT_ARRAY(mReports)) AS item WITH OFFSET
)
SELECT
  your_primary_key,
  -- 按组号将字段透视到对应列
  MAX(IF(group_num = 1, task_id, NULL)) AS TaskId1,
  MAX(IF(group_num = 1, report_type, NULL)) AS ReportType1,
  MAX(IF(group_num = 2, task_id, NULL)) AS TaskId2,
  MAX(IF(group_num = 2, report_type, NULL)) AS ReportType2,
  MAX(IF(group_num = 3, task_id, NULL)) AS TaskId3,
  MAX(IF(group_num = 3, report_type, NULL)) AS ReportType3
FROM parsed_data
GROUP BY your_primary_key

代码说明 #

1. 解析JSON数组：通过JSON_EXTRACT_ARRAY把mReports的字符串转为JSON数组，再用UNNEST拆分成单行，同时用WITH OFFSET记录每个元素在数组中的位置。
2. 提取目标字段：用JSON_VALUE从每个拆分后的JSON对象中精准提取taskId和reportType，并将偏移量+1得到从1开始的组编号。
3. 透视成列：利用MAX(IF(...))将不同组的字段聚合到同一行的对应列中，最后按原表主键分组，确保每行原始数据对应一行结果。

这种方法比正则更稳定，不会因为JSON字符串的格式变化（比如空格、转义符）导致提取失败，同时能轻松支持最多3组的需求。

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