在BigQuery中动态解析并转置JSON列内changed_happened数据

问题背景 #

在BigQuery某数据表中，有一列存储着JSON格式数据，需首次处理这类数据：动态解析JSON，并将其中changed_happened节点下的所有数据转置为列格式。

示例JSON数据：

{
  "changed_happened": [
    ["company_name", null, "test"],
    ["conversion", "1.21810222123", "1.65272222"],
    ["base_total", "$ 12,000.000", "$ 1,0000"],
    ["base_net_total", "$ 12,100,00", "$ 1,100.00"],
    ["total", "\u00a3 5,000.00", "\u00a3 2,000.00"],
    ["net_total", "5,000.00", "3,000.00"],
    ["net_total", 0.0, "2,500.00"],
    ["tax", 0.0, "$ 1,652.70"],
    ["grand_total", "$ 12,181.02", "$ 1,652.70"],
    ["words", "SGD Twelve Thousand, One Hundred And Eighty One and Two Cent only.", "SGD One Thousand, Six Hundred And Fifty Two and Seventy Cent only."],
    ["total", "\u00a3 10,000.00", "\u00a3 1,000.00"],
    ["words", "GBP Ten Thousand only.", "GBP One Thousand only."],
    ["outstading", "$ 10,000.00", "$ 1,652.70"],
    ["release_dt", null, ""]
  ]
}

预期转置后输出（中文列名示例）：
公司名称 | 转换率 | 基准总额 | 基准净额 | 总额1 | 总额2 | 净额1 | 净额2 | 税费 | 总计 | 金额文字描述1 | 金额文字描述2 | 未结金额 | 发布日期

解决方案 #

1. 静态转置SQL（适合已知字段名场景）

假设数据表名为your_table，JSON列名为json_column，表中存在唯一标识字段id：

WITH parsed_data AS (
  SELECT
    JSON_EXTRACT_ARRAY(json_column, '$.changed_happened') AS changes_array,
    id AS record_id
  FROM `your_table`
),
unnested_changes AS (
  SELECT
    record_id,
    change[OFFSET(0)] AS field_name,
    change[OFFSET(1)] AS old_value,
    change[OFFSET(2)] AS new_value,
    ROW_NUMBER() OVER(PARTITION BY record_id, field_name) AS field_seq
  FROM parsed_data,
    UNNEST(changes_array) AS change
)
SELECT
  record_id,
  MAX(IF(field_name = 'company_name' AND field_seq = 1, new_value, NULL)) AS 公司名称,
  MAX(IF(field_name = 'conversion' AND field_seq = 1, new_value, NULL)) AS 转换率,
  MAX(IF(field_name = 'base_total' AND field_seq = 1, new_value, NULL)) AS 基准总额,
  MAX(IF(field_name = 'base_net_total' AND field_seq = 1, new_value, NULL)) AS 基准净额,
  MAX(IF(field_name = 'total' AND field_seq = 1, new_value, NULL)) AS 总额1,
  MAX(IF(field_name = 'total' AND field_seq = 2, new_value, NULL)) AS 总额2,
  MAX(IF(field_name = 'net_total' AND field_seq = 1, new_value, NULL)) AS 净额1,
  MAX(IF(field_name = 'net_total' AND field_seq = 2, new_value, NULL)) AS 净额2,
  MAX(IF(field_name = 'tax' AND field_seq = 1, new_value, NULL)) AS 税费,
  MAX(IF(field_name = 'grand_total' AND field_seq = 1, new_value, NULL)) AS 总计,
  MAX(IF(field_name = 'words' AND field_seq = 1, new_value, NULL)) AS 金额文字描述1,
  MAX(IF(field_name = 'words' AND field_seq = 2, new_value, NULL)) AS 金额文字描述2,
  MAX(IF(field_name = 'outstading' AND field_seq = 1, new_value, NULL)) AS 未结金额,
  MAX(IF(field_name = 'release_dt' AND field_seq = 1, new_value, NULL)) AS 发布日期
FROM unnested_changes
GROUP BY record_id;

2. 动态转置SQL（适合字段未知或动态变化场景）

通过EXECUTE IMMEDIATE自动生成转置列，无需手动指定字段：

DECLARE columns STRING;

-- 自动生成所有转置列的SQL片段
SET columns = (
  SELECT STRING_AGG(
    DISTINCT CONCAT(
      'MAX(IF(field_name = "', field_name, '" AND field_seq = ', field_seq, ', new_value, NULL)) AS `', 
      CASE WHEN field_seq > 1 THEN CONCAT(REPLACE(field_name, '_', ' '), ' ', field_seq) ELSE REPLACE(field_name, '_', ' ') END, '`'
    ), ', '
  )
  FROM (
    SELECT
      field_name,
      ROW_NUMBER() OVER(PARTITION BY field_name) AS field_seq
    FROM (
      SELECT DISTINCT change[OFFSET(0)] AS field_name
      FROM `your_table`,
        UNNEST(JSON_EXTRACT_ARRAY(json_column, '$.changed_happened')) AS change
    )
  )
);

-- 执行动态生成的SQL
EXECUTE IMMEDIATE CONCAT(
  'WITH parsed_data AS (
    SELECT
      JSON_EXTRACT_ARRAY(json_column, "$.changed_happened") AS changes_array,
      id AS record_id
    FROM `your_table`
  ),
  unnested_changes AS (
    SELECT
      record_id,
      change[OFFSET(0)] AS field_name,
      change[OFFSET(2)] AS new_value,
      ROW_NUMBER() OVER(PARTITION BY record_id, field_name) AS field_seq
    FROM parsed_data,
      UNNEST(changes_array) AS change
  )
  SELECT record_id, ', columns, '
  FROM unnested_changes
  GROUP BY record_id'
);

注意事项 #

• 示例中默认提取changed_happened子数组的第三个元素（新值），若需展示旧值，可将new_value替换为old_value，或同时添加旧值列
• 重复字段通过field_seq序号区分，生成的列名会自动添加序号后缀（如净额1、净额2）
• 字段名中的下划线会被替换为空格，生成更易读的中文列名，可根据需求调整替换规则

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