如何在BigQuery中提取JSON对象唯一键值对并展开regularHours数组

因为你的每条记录里$.menus下只有一个唯一键，我们可以通过以下步骤提取并展开对应的regularHours数组：

方法一：简洁写法 #

直接嵌套函数完成提取与展开：

SELECT
  hour.*
FROM MyDataSet.UberEats,
UNNEST(
  JSON_QUERY_ARRAY(
    JSON_QUERY(JSON_EXTRACT(response.data, '$.menus'), '$[0]'),
    '$.regularHours'
  )
) AS hour

方法二：分步解析（更易读） #

用CTE分步处理，便于理解每一步的结果：

WITH menu_objects AS (
  SELECT
    -- 将menus下的唯一键对象提取出来
    JSON_QUERY(JSON_EXTRACT(response.data, '$.menus'), '$[0]') AS single_menu
  FROM MyDataSet.UberEats
)
SELECT
  -- 展开regularHours数组为多行
  hour.*
FROM menu_objects,
UNNEST(JSON_QUERY_ARRAY(single_menu, '$.regularHours')) AS hour

关键函数说明 #

• JSON_EXTRACT(response.data, '$.menus')：获取包含唯一键的整个menus对象
• JSON_QUERY(..., '$[0]')：将menus对象转换为数组后取第一个元素（即唯一键对应的完整对象）
• JSON_QUERY_ARRAY(..., '$.regularHours')：从目标对象中提取regularHours数组
• UNNEST：把数组展开成独立的行记录

如果需要保留原表的其他字段，只需在SELECT中添加原表字段即可：

SELECT
  original.*,
  hour.*
FROM MyDataSet.UberEats original,
UNNEST(
  JSON_QUERY_ARRAY(
    JSON_QUERY(JSON_EXTRACT(original.response.data, '$.menus'), '$[0]'),
    '$.regularHours'
  )
) AS hour

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