如何在BigQuery中提取嵌套列表数据？

原始数据（SQL定义） #

WITH sample_table_1 AS (
  SELECT '''{
   "id": 1,
   "data" : "[[\"Name\",\"Quantity\"],[\"Woody\",5],[\"Buzz\",42],[\"Potato\",20]]"
  }  
  ''' json
)

,sample_table_2 AS (
  SELECT '''{
    "id": 2,
   "data" : "[[\"Genre\",\"Name\",\"Sales\"],[\"Cartoon\",\"Sponge\",10],[\"Cartoon\",\"Patrick\",8],[\"Anime\",\"Naruto\",21]]"
  }  
  ''' json
)

期望输出结果 #

解决方案（以BigQuery SQL为例） #

要实现这个需求，核心是解析嵌套JSON列表，把首行作为表头、后续行作为对应数据，再合并两个表的结果并补全缺失字段。以下是具体SQL：

WITH sample_table_1 AS (
  SELECT '''{
   "id": 1,
   "data" : "[[\"Name\",\"Quantity\"],[\"Woody\",5],[\"Buzz\",42],[\"Potato\",20]]"
  }  
  ''' json
)
,sample_table_2 AS (
  SELECT '''{
    "id": 2,
   "data" : "[[\"Genre\",\"Name\",\"Sales\"],[\"Cartoon\",\"Sponge\",10],[\"Cartoon\",\"Patrick\",8],[\"Anime\",\"Naruto\",21]]"
  }  
  ''' json
),
combined AS (
  SELECT * FROM sample_table_1 UNION ALL SELECT * FROM sample_table_2
),
parsed AS (
  SELECT
    JSON_VALUE(json, '$.id') AS id,
    JSON_PARSE(JSON_VALUE(json, '$.data')) AS data_array
  FROM combined
),
split_rows AS (
  SELECT
    id,
    arr_element,
    OFFSET(arr_index) AS row_idx
  FROM parsed,
    UNNEST(data_array) AS arr_element WITH OFFSET arr_index
),
header_rows AS (
  SELECT
    id,
    arr_element AS headers
  FROM split_rows
  WHERE row_idx = 0
),
data_rows AS (
  SELECT
    id,
    arr_element AS values,
    row_idx
  FROM split_rows
  WHERE row_idx > 0
),
key_value_pairs AS (
  SELECT
    d.id,
    h.headers[OFFSET(val_idx)] AS key,
    d.values[OFFSET(val_idx)] AS value
  FROM data_rows d
  JOIN header_rows h ON d.id = h.id,
    UNNEST(d.values) AS value WITH OFFSET val_idx
),
pivoted AS (
  SELECT
    id,
    JSON_OBJECT_AGG(key, value) AS row_data
  FROM key_value_pairs
  GROUP BY id, row_idx
)
SELECT
  id,
  JSON_VALUE(row_data, '$.Name') AS Name,
  JSON_VALUE(row_data, '$.Quantity') AS Quantity,
  JSON_VALUE(row_data, '$.Genre') AS Genre,
  JSON_VALUE(row_data, '$.Sales') AS Sales
FROM pivoted
ORDER BY id, Name;

步骤说明 #

1. 合并表：用UNION ALL把两个样本表合并成一个数据集。
2. 解析JSON：提取id字段，把data字符串转成JSON数组。
3. 拆分数组行：拆分数组并保留索引，区分出首行（表头）和后续的数据行。
4. 匹配表头与数据：将每个数据行的元素和对应表头字段一一配对，生成键值对。
5. 转成结构化数据：把键值对聚合为JSON对象，再提取出需要的字段，缺失字段自动显示为空。

内容的提问来源于stack exchange，提问作者Kevin Adrian Halim