如何在BigQuery中提取UberEats动态键JSON内的营业时间

问题背景 #

你的原查询通过硬编码菜单ID（26bd579e-5664-4f0a-8465-2f5eb5fbe705）能获取数据，但response列中的菜单键是动态生成的，无法固定取值。需要通过动态遍历菜单对象的所有键来实现通用提取。

解决方案查询 #

以下BigQuery查询可动态处理所有菜单键，同时遵循你指定的规则（取第一个section、解析daysBitArray和所有regularHours元素）：

SELECT
  vb_name,
  -- 将daysBitArray的索引转换为星期名称（周一为索引0）
  CASE day_index
    WHEN 0 THEN '周一'
    WHEN 1 THEN '周二'
    WHEN 2 THEN '周三'
    WHEN 3 THEN '周四'
    WHEN 4 THEN '周五'
    WHEN 5 THEN '周六'
    WHEN 6 THEN '周日'
  END AS applicable_day,
  JSON_EXTRACT_SCALAR(regular_hour, '$.startTime') AS start_time,
  JSON_EXTRACT_SCALAR(regular_hour, '$.endTime') AS end_time
FROM
  `arboreal-vision-339901.take_home_v2.virtual_kitchen_ubereats_hours`,
  -- 提取menus下的所有动态键
  UNNEST(JSON_OBJECT_KEYS(JSON_EXTRACT(response, '$.data.menus'))) AS menu_key,
  -- 获取当前菜单的第一个section
  UNNEST(JSON_QUERY_ARRAY(JSON_EXTRACT(response, CONCAT('$.data.menus.', menu_key)), '$.sections[0:1]')) AS menu_section,
  -- 展开当前section的所有regularHours元素
  UNNEST(JSON_QUERY_ARRAY(menu_section, '$.regularHours')) AS regular_hour,
  -- 展开daysBitArray的索引（0-6对应周一到周日）
  UNNEST(GENERATE_ARRAY(0, 6)) AS day_index
WHERE
  -- 只保留该时段适用的星期
  JSON_EXTRACT_ARRAY_ELEMENT_TEXT(JSON_EXTRACT(menu_section, '$.daysBitArray'), day_index) = 'true'

核心逻辑说明 #

• 动态遍历菜单键：用JSON_OBJECT_KEYS获取$.data.menus下的所有键，替代硬编码的菜单ID。
• 取第一个section：通过$.sections[0:1]切片获取数组的第一个元素，再用UNNEST展开。
• 解析星期适用情况：展开daysBitArray的索引（0对应周一），通过CASE转换为星期名称，并过滤出适用的星期。
• 扁平化营业时间数组：UNNEST处理regularHours数组，提取所有时段的起止时间。

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