根据另一列中的值更新BigQuery中的嵌套数组 [英] Updating nested array in BigQuery based on value in another column

问题描述

我对BigQuery语法有疑问，我对此有些犹豫.我在一个表中有一个条目，该表具有多个键值对作为数组，并且当键是某个值时，我只想更新值中的单个特定字符串.

I have a problem with BigQuery syntax that I'm a bit stuck on. I have an entry in a table that has multiple key value pairs as an array and I would like to update only a single specific string in the values when the key is a certain value.

这是条目

[
  {
    "event_params": [
      {
        "key": "programType",
        "value": {
          "string_value": "custom",
          "int_value": null,
          "float_value": null,
          "double_value": null
        }
      },
      {
        "key": "firebase_event_origin",
        "value": {
          "string_value": "app",
          "int_value": null,
          "float_value": null,
          "double_value": null
        }
      },
      {
        "key": "firebase_screen_id",
        "value": {
          "string_value": null,
          "int_value": "5",
          "float_value": null,
          "double_value": null
        }
      },
      {
        "key": "programName",
        "value": {
          "string_value": "overwrite_me",
          "int_value": null,
          "float_value": null,
          "double_value": null
        }
      }
    ]
  }
]

，除"key" ="programName"外，我希望其他所有内容保持不变，我想用新字符串"anonymous"覆盖string_value"overwrite_me".通常，string_value是任意字符串，我只希望它被相同的值覆盖.

and I would like to keep everything the same except when "key" = "programName" I want to overwrite the string_value "overwrite_me" with the new string "anonymous". In general, the string_value is some arbitrary string and I just want it overwritten with the same value.

根据此处和

Based on a few answers here and here I have tried the following query (and various permutations of it)

SET
event_params = ARRAY(
   SELECT AS STRUCT * REPLACE(
     CASE WHEN event_param.key = 'programName' THEN
      ((SELECT AS STRUCT value.* REPLACE('anonymous' AS string_value)) AS value)
    END
  ) 
  FROM UNNEST(event_params) as event_param 
)

但是BigQuqery总是给我语法错误，特别是语法错误:预期为)"，但关键字AS为[9:73].我不太确定自己在做什么错，我很困惑.

but BigQuqery always gives me syntax errors, specifically "Syntax error: Expected ")" but got keyword AS at [9:73]". I'm not quite sure what I'm doing wrong and I'm quite puzzled.

推荐答案

以下是BigQuery标准SQL

Below is for BigQuery Standard SQL

查看您的代码的更正版本

See corrected version of very yours code

SET
event_params = ARRAY(
  SELECT AS STRUCT * REPLACE(
    CASE WHEN event_param.key = 'programName' THEN
      (SELECT AS STRUCT * REPLACE('anonymous' AS string_value) FROM UNNEST([value]))
    ELSE value
   END AS value
  ) 
  FROM UNNEST(event_params) AS event_param 
)

另外，请注意-您可以删除对 event_param 的引用，如下例所示

Also, note - you can remove reference to event_param as in below example

SET
event_params = ARRAY(
  SELECT AS STRUCT * REPLACE(
    CASE WHEN key = 'programName' THEN
      (SELECT AS STRUCT * REPLACE('anonymous' AS string_value) FROM UNNEST([value]))
    ELSE value
   END AS value
  ) 
  FROM UNNEST(event_params)  
)

这篇关于根据另一列中的值更新BigQuery中的嵌套数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！