如何在BigQuery中递归提取JSON字典列表的start值为数组？

假设你的表中metadata列包含嵌套的JSON数组mentions，要提取所有元素的start字段值生成数组[3,19,35]，且不通过索引（如[0]）直接访问元素，可采用以下两种方法：

方法一：数组推导式（简洁高效） #

利用UNNEST遍历数组元素，结合ARRAY()构造结果数组，无需手动指定索引：

SELECT
  ARRAY(
    SELECT CAST(JSON_VALUE(mention, '$.start') AS INT64)
    FROM UNNEST(JSON_EXTRACT_ARRAY(metadata, '$.mentions')) AS mention
  ) AS start_values
FROM your_table

逻辑说明 #

1. JSON_EXTRACT_ARRAY(metadata, '$.mentions') 将mentions字段解析为BigQuery数组
2. UNNEST 把数组展开为行，每行对应一个mentions元素
3. 用JSON_VALUE提取每个元素的start值并转为整数，最后通过ARRAY()重新组合为数组

方法二：递归CTE（真正递归实现） #

如果需要严格意义上的递归逻辑，可使用递归公共表表达式（CTE）遍历数组：

WITH recursive_extract AS (
  -- 初始步骤：取数组第一个元素的start值
  SELECT
    metadata,
    JSON_EXTRACT_ARRAY(metadata, '$.mentions') AS mentions_array,
    0 AS current_index,
    [CAST(JSON_VALUE(JSON_EXTRACT_ARRAY(metadata, '$.mentions')[OFFSET(0)], '$.start') AS INT64)] AS start_values
  FROM your_table
  WHERE ARRAY_LENGTH(JSON_EXTRACT_ARRAY(metadata, '$.mentions')) > 0

  UNION ALL

  -- 递归步骤：依次取下一个元素的start值，拼接到结果数组
  SELECT
    metadata,
    mentions_array,
    current_index + 1,
    ARRAY_CONCAT(start_values, [CAST(JSON_VALUE(mentions_array[OFFSET(current_index + 1)], '$.start') AS INT64)])
  FROM recursive_extract
  WHERE current_index + 1 < ARRAY_LENGTH(mentions_array)
)
-- 取遍历完成的最终结果
SELECT
  metadata,
  start_values
FROM recursive_extract
WHERE current_index + 1 = ARRAY_LENGTH(mentions_array)

逻辑说明 #

1. 初始CTE行读取数组第一个元素的start值，初始化结果数组和索引
2. 递归步骤每次将索引+1，提取对应位置元素的start值并拼接到结果数组
3. 当索引到达数组末尾时，停止递归并输出最终的start_values数组

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