BigQuery update table with SELECT STRUCT unexplainable behaviour

Question

I have a bigquery table with schema as shown in image, where field_b and field_c are nested within field_a (array)

I then have a function that runs a query that matches certain value and try to update field_a

query:

UPDATE `{BQ_METADATA_TABLE_A}`
            SET field_a = ARRAY(
                SELECT STRUCT(
                    a.abc AS abc,
                    a.def AS def,
                    a.ghi AS ghi,
                    a.hik AS hik,
                    "@var_b" AS field_b,
                    field_c AS field_c
                )
                FROM UNNEST(field_a) a
            )
            WHERE EXISTS (
                SELECT 1 
                FROM UNNEST(a) 
                WHERE ghi = "@some_value"
            )

the query runs successfully with matched value "@some_value" and update with @var_b, problem is it's updating field_c rather than field_b, even though i stated explicitly that field_b is to update and field_c stay with its own value. field_b and field_c are both initially null values.

what's the issue here? as a workaround i set both fields with "@var_b", like "@var_b" AS field_b, "@var_b" AS field_c

so that both gets updated.

marky · Accepted Answer · 2024-12-16 19:39:50Z

0

As for the field C to be not updating, you might want to explicitly reference the field\_c in the field\_a that is still UNNEST, you can try to update below:

from

"@var\_b" AS field\_b,

field\_c AS field\_c

to

"@var\_b" AS field\_b,

a.field\_c AS field\_c

answered Dec 16, 2024 at 19:39

marky

5102 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

BigQuery update table with SELECT STRUCT unexplainable behaviour

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related