Big Query动态解析JSON获取门店营业时间问题求助

核心思路 #

按照需求规则，逐层解析JSON结构：

• 提取menus字典的第一组键值对
• 从该值中取第一个section
• 解析section中的daysBitArray（周一为起始标记）和regularHours数组的起止时间

示例JSON结构（response列） #

假设response列的JSON格式如下：

{
  "menus": {
    "menu_123": {
      "sections": [
        {
          "daysBitArray": "1111100",
          "regularHours": [
            {"start": "09:00", "end": "18:00"},
            {"start": "10:00", "end": "19:00"}
          ]
        },
        {...}
      ]
    },
    "menu_456": {...}
  },
  "menuUUID": "menu_123"
}

正确的BigQuery SQL实现 #

WITH parsed_data AS (
  SELECT
    menuUUID,
    -- 获取menus的第一个键（即第一个菜单ID）
    JSON_VALUE(JSON_KEYS(response, '$.menus')[OFFSET(0)]) AS first_menu_key,
    -- 提取第一个菜单的完整JSON
    JSON_EXTRACT(response, CONCAT('$.menus.', JSON_VALUE(JSON_KEYS(response, '$.menus')[OFFSET(0)]))) AS first_menu,
    response
  FROM your_table_name
)
SELECT
  menuUUID,
  -- 解析第一个section的daysBitArray
  JSON_VALUE(first_menu_section, '$.daysBitArray') AS days_bit_array,
  -- 展开regularHours数组，提取每个时段的起止时间
  JSON_VALUE(regular_hour, '$.start') AS opening_time,
  JSON_VALUE(regular_hour, '$.end') AS closing_time
FROM parsed_data,
-- 提取第一个section
UNNEST([JSON_EXTRACT(first_menu, '$.sections[0]')]) AS first_menu_section,
-- 展开regularHours数组
UNNEST(JSON_QUERY_ARRAY(first_menu_section, '$.regularHours')) AS regular_hour

关键说明 #

1. 获取menus的第一个键：使用JSON_KEYS(response, '$.menus')获取menus的所有键，再用[OFFSET(0)]取第一个键，避开动态拼接JSON路径的限制。
2. 提取第一个section：通过$.sections[0]直接定位数组第一个元素（BigQuery中JSON数组索引从0开始）。
3. 展开regularHours数组：用JSON_QUERY_ARRAY将数组转为可UNNEST的结构，逐个提取每个时段的起止时间。
4. 原SQL无数据返回的常见原因：
   • 错误使用数组索引（比如用[1]而非[0]）
   • 未正确处理menus的键提取，导致JSON路径无效
   • response列存在NULL或格式错误的JSON，可添加WHERE response IS NOT NULL AND JSON_VALID(response)过滤

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