在BigQuery中如何用JSON_EXTRACT/REGEXP_EXTRACT提取指定JSON子段？

我有一个包含各类嵌套JSON字段的长JSON列，希望提取名为regularHours的特定字段（其值为结构体数组）。尝试使用JSON_EXTRACT和REGEXP_EXTRACT两种方法，但无法完整提取该字段。

目标提取的JSON片段：

"regularHours":[
        {"daysBitArray":[true,false,false,false,false,false,false],"endTime":"01:00","startTime":"10:30"},
        {"daysBitArray":[false,true,false,false,false,false,false],"endTime":"01:00","startTime":"10:30"},
        {"daysBitArray":[false,false,true,false,false,false,false],"endTime":"01:00","startTime":"10:30"},
        {"daysBitArray":[false,false,false,true,false,false,false],"endTime":"01:00","startTime":"10:30"},
        {"daysBitArray":[false,false,false,false,true,false,false],"endTime":"01:00","startTime":"10:30"},
        {"daysBitArray":[false,false,false,false,false,true,false],"endTime":"01:00","startTime":"10:30"},
        {"daysBitArray":[false,false,false,false,false,false,true],"endTime":"21:30","startTime":"10:30"}
               ]

JSON大致结构：

data        
   menuMapping      [2]
   menuStructures       {1}
   menus        {1}
           d62628f4-9b10-4099-aed0-69f904a3a91e     {6}
                 createdAt  :   2020-11-10T19:53:00.244Z
                 displayOptions     {3}
                      currencyCode  :   USD
                      disableItemInstructions   :   false
                      isMenuV2  :   true
                 entities   :   null
                 sections       [1]
                    0       {12}
                        createdAt   :   2020-11-11T07:10:54.511Z
                        externalNotes   :   
                        image   :   null
                        invisible   :   false
                        regularHours        [7]

1. 使用REGEXP_EXTRACT的正则表达式 #

由于JSON存在嵌套结构，需确保正则能匹配完整的数组（包括内部的嵌套数组）：

• 如果你的SQL引擎支持递归正则（如PostgreSQL、部分版本MySQL），可使用：

  "regularHours":(\[(?:[^[\]]|(?R))*\])
  

  该正则会递归匹配数组内的嵌套结构，确保提取完整的regularHours数组内容，取第一个捕获组即可。

• 如果引擎不支持递归正则，可退而求其次（假设regularHours数组闭合方括号后紧跟逗号或大括号，符合常规JSON语法）：

  "regularHours":\[([\s\S]*?)\]\s*(?:,|})
  

  注意：极端嵌套场景下可能出错，但多数常规JSON结构中可正常工作。

2. 使用JSON_EXTRACT的动态路径 #

针对menus下的动态标识符，用JSONPath的**通配符***匹配所有未知键，路径如下：

$.data.menus.*.sections[*].regularHours

• $.data.menus.*：匹配menus下的所有子对象（忽略键名）
• .sections[*]：匹配sections数组中的所有元素
• .regularHours：提取目标字段

不同SQL引擎的具体写法：

• BigQuery: JSON_EXTRACT(json_column, '$.data.menus.*.sections[*].regularHours')
• MySQL: JSON_EXTRACT(json_column, '$.data.menus.*.sections[*].regularHours')
• PostgreSQL: json_column #> '{data, menus, *, sections, *, regularHours}'

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