如何在BigQuery(SQL)中解析UberEats JSON数据获取时段信息

针对你遇到的JSON解析问题——动态UUID键导致结果不全、部分字段返回null，这里提供一套基于BigQuery原生JSON函数的解决方案，优先取店铺最新时间戳记录，无需枚举日期或使用正则：

核心思路 #

1. 先筛选每个店铺的最新记录，避免处理过时数据；
2. 动态解析menus下的UUID键（不用硬编码固定值）；
3. 展平营业时间数组，提取所需字段。

完整SQL代码 #

WITH latest_shop_data AS (
  SELECT
    -- 替换为表中实际的店铺标识字段
    shop_id,
    -- 替换为存储UberEats JSON数据的字段名
    json_content,
    -- 按店铺分组，取时间戳最新的记录
    ROW_NUMBER() OVER (PARTITION BY shop_id ORDER BY update_timestamp DESC) AS row_rank
  FROM
    `arboreal-vision-339901.take_home_v2.virtual_kitchen_ubereats_hours`
),
extract_hours_array AS (
  SELECT
    shop_id,
    -- 动态获取menus下的唯一UUID键，拼接成完整JSON路径
    JSON_EXTRACT_ARRAY(
      json_content,
      CONCAT('$.menus.', JSON_VALUE(JSON_KEYS(json_content, '$.menus')[OFFSET(0)]), '.hours')
    ) AS operating_hours
  FROM
    latest_shop_data
  WHERE row_rank = 1 -- 仅保留每个店铺的最新记录
)
SELECT
  -- 提取营业时间核心字段
  JSON_VALUE(hour_entry, '$.WeekDay') AS WeekDay,
  JSON_VALUE(hour_entry, '$.startTime') AS startTime,
  JSON_VALUE(hour_entry, '$.endTime') AS endTime,
  shop_id -- 可选：保留店铺标识用于后续关联
FROM
  extract_hours_array,
  -- 展平营业时间数组，每个时段单独生成一行
  UNNEST(operating_hours) AS hour_entry;

关键步骤说明 #

• 筛选最新记录：用ROW_NUMBER()窗口函数按店铺分组，按时间戳倒序排序，只取row_rank=1的记录，确保处理的是每个店铺的最新数据。
• 动态处理UUID键：JSON_KEYS(json_content, '$.menus')返回menus下的所有键（即店铺UUID），[OFFSET(0)]取第一个（根据你的描述，每个店铺仅对应一个UUID键），再用CONCAT拼接成完整JSON路径，解决UUID不固定的问题。
• 展平数组：UNNEST(operating_hours)将JSON数组拆分为多行，每个时段单独成为一条记录。
• 提取字段：用JSON_VALUE从每个时段的JSON对象中提取WeekDay、startTime、endTime，避免正则匹配的不稳定。

注意事项 #

• 请将代码中的shop_id、json_content、update_timestamp替换为表中实际的字段名；
• 若部分店铺的menus下存在多个UUID键，可将[OFFSET(0)]改为UNNEST(JSON_KEYS(...))遍历所有键，根据业务需求调整逻辑；
• 若startTime/endTime仍返回null，需检查JSON结构是否一致，确认这些字段存在于每个时段的JSON对象中。

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