更新BigQuery表中的ARRAY中的值 [英] Updating a value in an ARRAY in a BigQuery table

问题描述

我有一个BigQuery表，该表具有与图片所示相似的架构，其中的行可以容纳结构数组.我应该使用什么语法来尝试将itemDelta.typeId更改/更新为另一个值? (如果不清楚，请参见SQL)

I have a BigQuery table with a similar schema to that shown in the picture, rows that can hold arrays of structs. What syntax should I use to attempt to change/update an itemDelta.typeId to another value? (see SQL if unclear)

BigQuerySchema

UPDATE itemInformation
SET    itemDelta.typeId = 9426
WHERE  itemDelta.typeId = 2424; 

更新:

我发现的一种解决方法是实现用户定义的功能. BigQuery允许您使用JavaScript创建自定义函数.

One work around I found is to implement a User-Defined-Function. BigQuery allows you to create custom functions using JavaScript.

这是代码:

CREATE TEMPORARY FUNCTION edit(table ARRAY<STRUCT<typeId INT64, amount INT64>>)
RETURNS ARRAY<STRUCT<typeId INT64, amount INT64>>
LANGUAGE js AS """
var i;  
for(i = 0; i < table.length; i++)
{
  if(table[i].typeId == 2452)
  {
    table[i].typeId = 1000
  }
}
return table;
""";

UPDATE itemInformation
SET    itemDelta = edit(itemDelta)
where  true

推荐答案

尝试一下:

UPDATE itemInformation
SET    itemDelta = ARRAY(SELECT AS STRUCT * REPLACE(9426 AS typeId) FROM UNNEST(itemDelta))
WHERE  2424 IN (SELECT typeId FROM UNNEST(itemDelta));

过滤器不是严格必需的，但是由于它不会修改typeId在数组中具有不同值的行，因此它可能会使查询稍微快一些.

The filter isn't strictly necessary, but it may make the query slightly faster since it doesn't modify rows where typeId has different values in the array.

这篇关于更新BigQuery表中的ARRAY中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！