sqlgoogle-bigqueryunnest

How do I add column names to a select that is doing unnest struct in BigQuery


I am constructing a table using given values like this

SELECT * FROM UNNEST
([ STRUCT
(1, 3.07766, 6.31371, 12.7062, 63.65600),
(2, 1.88562, 2.91999, 4.30265, 9.92482),
(3, 1.63774, 2.35336, 3.18243, 5.84089)
]) 

which creates columns with names fn_ as this

Row f0_ f1_ f2_ f3_ f4_

I would like to name columns without putting AS statements into STRUCT, so not like

SELECT * FROM UNNEST
([ STRUCT
(1 as a, 3.07766 as b, 6.31371 as c, 12.7062 as d, 63.65600 as e),
(2, 1.88562, 2.91999, 4.30265, 9.92482),
(3, 1.63774, 2.35336, 3.18243, 5.84089)
]) 

I understand that it works, but the question is can we do something like

SELECT * FROM UNNEST
([ STRUCT
(1, 3.07766, 6.31371, 12.7062, 63.65600),
(2, 1.88562, 2.91999, 4.30265, 9.92482),
(3, 1.63774, 2.35336, 3.18243, 5.84089)
]) as (a, b, c, d, e)

and that is because this SELECT is constructed by Python code to be used in MERGE statement that updates existing values in a table with a constructed table.

If there is any other way you think it's better to construct temporary table with given values in BigQuery, please share your ideas!


Solution

  • Use below option

    SELECT * FROM UNNEST
    ([ STRUCT<a int64, b float64, c float64, d float64, e float64>
    (1, 3.07766, 6.31371, 12.7062, 63.65600),
    (2, 1.88562, 2.91999, 4.30265, 9.92482),
    (3, 1.63774, 2.35336, 3.18243, 5.84089)
    ])        
    

    from your Python code point of view - it should be equivalent to what you want. So that line with column names - moved to STRUCT definition line and does not touch the values rows