更新嵌套的bigquery json元素

tjvv9vkg 于 2021-07-29 发布在 Java

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

我有这样一个大的查询模式：

visitorId                    INTEGER NULLABLE
visitID                      INTEGER NULLABLE
hits                         RECORD  REPEATED
hits.eventInfo               RECORD  NULLABLE   
hits.eventInfo.eventCategory STRING  NULLABLE   
hits.eventInfo.eventLabel    STRING  NULLABLE

样本数据如下：

visitorId  visitId  hits.eventInfo.eventCategory hits.eventInfo.eventCategory
123456     1        abc                          {"info":"secret", "otherfields":"blah"}
                    lmn                          {"info":"secret", "otherfields":"blah"}
                    xyz                          {"info":"secret", "otherfields":"blah"}
124557     1        abc                          {"info":"secret", "otherfields":"blah"}
                    lmn                          {"info":"secret", "otherfields":"blah"}
                    xyz                          {"info":"secret", "otherfields":"blah"}

我需要删除“info”：“secret”，只有当eventcategory是“”时。
我是个大问题新手。经过多次打击和尝试，我能够做到这一点，但不幸的是，现在卡住了。

UPDATE `project.dataset.ga_sessions_20200608`
SET hits = ARRAY(
  SELECT AS STRUCT * REPLACE((REGEXP_REPLACE(eventInfo.eventLabel, r"\"info\":\"[a-z A-Z]*\",", "")) AS eventInfo.eventLabel) from UNNEST(hits) 
)
WHERE (select eventInfo.eventLabel from UNNEST(hits)) LIKE '%info%'

这里有两个问题。
设置部件不工作：（
其中的子查询（subselect）未提供标量输出：'(
任何帮助，指针将不胜感激。

sql google-bigquery sql-update Nested

来源：https://stackoverflow.com/questions/62294110/update-nested-bigquery-json-element

1条答案

按热度按时间

eqzww0vc1#

用dml方法实现这一点很有挑战性( UPDATE )相对于在整个表上运行的批处理过程，有几个原因（您已经给出了一些原因）：
记录中需要更新的子字段是json字符串
更新的本质是在字符串表示中按字段键进行过滤
记录本身是表顶层的重复类型，并且包含结构
这就使得你不得不重建这两者的整个结构 hits 以及 eventInfo 为了替换它的一个字段（它本身是一个json编码的结构，尽管bigquery对此有点盲目，并将其视为一个字符串）。
afaik bigquery没有从json封送真正结构的函数，因此regex可能是实际消除json编码列中不需要的字段的唯一方法。
这里的where条件可以利用 JSON_EXTRACT_SCALAR standardsql函数来捕获要删除的特定键在json中实际存在的条件。
因此，一种可能的纯bigquery方法（没有UDF）可能如下所示：


# standardSQL

UPDATE
  `project.dataset.table_DATE`
SET
  --reconstruct hits column as an array
  hits = ARRAY(
  SELECT
       --reconstruct each struct of hits
    AS STRUCT * REPLACE( (
      SELECT
        --reconstruct eventInfo...
        AS STRUCT eventInfo.* REPLACE(
          --with eventLabel replaced with a filtered version
          REGEXP_REPLACE(eventInfo.eventLabel, r"\"secret\":\"[A-Za-z\p{L}]* [A-Za-z\p{L}]*\",", "") AS eventLabel)
        ) AS eventInfo )
  FROM
    UNNEST(hits) AS hits )
WHERE
  --Only for rows where at least one eventLabel's json contains a `secret` key
  EXISTS (SELECT JSON_EXTRACT_SCALAR(h.eventInfo.eventLabel, "$.secret") is not null from unnest(hits) as h)

注意正则表达式可能需要根据数据的格式进行一些更改。这里我假设一个两个单词的秘密值，可能包含unicode字符（比如名字+姓氏）。
正则表达式部分可能更好地由udf提供，尽管对于大型表来说，udf的速度可能较慢。

赞(0）回复(0）举报 2021-07-29

我来回答

更新嵌套的bigquery json元素

1条答案

相关问题

热门标签

最新问答