CODEWITHSUNDEEP
sqlgoogle-bigquery
Tobelawe
Tobelawe

Reputation: 41

Updating a value in an ARRAY in a BigQuery table

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;

UPDATE:

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

Here is the code:

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

Upvotes: 4

Views: 11857

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173190

#standardSQL
UPDATE `yourProject.yourTable.itemInformation`
SET itemDelta = ARRAY(
    SELECT STRUCT<typeId INT64, amount INT64>(IF(typeId = 2424, 9426, typeId), amount) 
    FROM UNNEST(itemDelta)
  ) 
WHERE 2424 IN (SELECT typeId FROM UNNEST(itemDelta))

Upvotes: 3

Elliott Brossard
Elliott Brossard

Reputation: 33765

Try this:

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

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.

Upvotes: 4

Related Questions