如何在BigQuery上使用JSON_EXTRACT/REGEXP_EXTRACT从长JSON中提取特定的子节？

xmjla07d 于 12个月前发布在其他

关注(0)|答案(1)|浏览(90)

我有一个很长的JSON列，其中有各种嵌套的JSON字段。我对一个名为regularHours的特定字段感兴趣，并希望提取与此嵌套键相关的所有值（值是结构体数组）。
如何使用JSON提取这些值？我尝试了两种方法-使用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]

（regularHours是我感兴趣的提取字段。
1.如果我使用REGEXP_EXTRACT，我应该使用什么Regex代码来提取regularHours数组？
1.如果我使用JSON_EXTRACT，我如何给予JSON路径给regularHours而不实际指定父路径？例如，上面的路径是-**"$.data.menus.d62628f4- 9 b10 -4099-aed 0 -69f904a3a91e.sections.0.regularHours”**然而，值“d 62628 f4 - 9 b10 -4099-aed 0 - 69 f904 a3 a91 e”是动态标识符，并且随着每个记录而改变。可以使用JSON_EXTRACT而不显式指定此键值吗？

regex

来源：https://stackoverflow.com/questions/77086559/how-to-extract-a-specfic-sub-section-from-a-long-json-using-json-extract-regexp

1条答案

按热度按时间

nkoocmlb1#

不幸的是，BigQuery的JSON处理并没有很好地设置来做这类事情。你可以通过定义一个自定义的JavaScript函数来提取值来解决这个问题：

CREATE TEMP FUNCTION get_regular_hours(input JSON) RETURNS ARRAY<JSON> LANGUAGE js AS """
  const menuKey = Object.keys(input.data.menus)[0];
  return input.data.menus[menuKey].sections[0].regularHours;
""";

然后你可以在查询中使用它：

WITH test AS (SELECT *
FROM UNNEST(
  [STRUCT<j JSON>
    (JSON '{ "data": { "menuMapping" : [1,2], "menus" : { "d62628f4-9b10-4099-aed0-69f904a3a91e" : { "createdAt" : "2020-11-10T19:53:00.244Z", "sections" : [ { "invisible" : false, "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"}               ]}]}}}}')])
)
SELECT get_regular_hours(j)
FROM test

我只使用了你的数据结构的一个示例，但它确实代表了与你在问题中相同的路径。
如果表中的JSON与输入格式不匹配，则需要在函数中使用可选链接来处理：

CREATE TEMP FUNCTION get_regular_hours(input JSON) RETURNS ARRAY<JSON> LANGUAGE js AS """
  const menuKey = Object.keys(input?.data?.menus || {})[0];
  return input?.data?.menus?.[menuKey]?.sections?.[0]?.regularHours || [];
""";

赞(0）回复(0）举报 12个月前

我来回答

如何在BigQuery上使用JSON_EXTRACT/REGEXP_EXTRACT从长JSON中提取特定的子节？

1条答案

相关问题

热门标签

最新问答