如何在BigQuery中解析含动态键的JSON并提取endTime值

针对response.data.menus后键名动态变化的场景，以下是具体的解析方法和路径定义逻辑：

核心思路 #

动态键无法通过固定路径直接定位，需用通配符*匹配所有同级动态键，将对象转换为数组后展开，再提取目标字段。

具体实现方法 #

方法1：批量提取所有动态键下的endTime #

如果menus下有多个动态键，需要提取所有对应的endTime，用UNNEST配合JSON_EXTRACT_ARRAY展开数组：

SELECT
  JSON_VALUE(menu_item, '$.endTime') AS endTime
FROM
  `your-project.your-dataset.your-table`,
  UNNEST(JSON_EXTRACT_ARRAY(json_column, '$.response.data.menus.*')) AS menu_item

• 路径$.response.data.menus.*：用*匹配menus下的所有动态键，返回所有键对应的值组成的数组
• UNNEST将数组拆分为单行记录，再通过JSON_VALUE提取每个记录中的endTime

方法2：提取单个动态键下的endTime #

如果menus下仅有一个动态键，可直接通过通配符定位后取第一个元素：

SELECT
  JSON_VALUE(JSON_QUERY(json_column, '$.response.data.menus.*[0]'), '$.endTime') AS endTime
FROM
  `your-project.your-dataset.your-table`

• $.response.data.menus.*[0]：匹配menus下的第一个动态键对应的值，再从中提取endTime

保留原表字段的写法 #

如果需要保留原表的其他字段，使用LEFT JOIN关联展开的数组：

SELECT
  t.*,
  JSON_VALUE(menu_item, '$.endTime') AS endTime
FROM
  `your-project.your-dataset.your-table` t
LEFT JOIN UNNEST(JSON_EXTRACT_ARRAY(t.json_column, '$.response.data.menus.*')) AS menu_item

路径定义关键点 #

• 动态键位置用*替代固定键名，匹配该层级下所有键
• 若目标字段在动态键的子层级，需先将动态键的值转为数组，再通过展开或索引定位后提取子字段
• 若endTime是数值类型，可替换JSON_VALUE为JSON_EXTRACT_SCALAR，或直接用CAST转换类型

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