Rename unamed column in bigquery standard-sql

0
votes

I'm trying to rename the first and only column generated by the function UNNEST(GENERATE_ARRAY(0,5,1)) so it can be used in a JOIN.

I've tried the solutions detailed in this article but it seems that STANDARD SQL doesn't support these kind of aliases: https://modern-sql.com/use-case/naming-unnamed-columns

2
google-bigquerybigquery-standard-sql
You have a low rate. Important on SO - you can mark accepted answer by using the tick on the left of the posted answer, below the voting. See meta.stackexchange.com/questions/5234/… for why it is important! Also important to vote on answer. Vote up answers that are helpful. ... You can check about what to do when someone answers your question - stackoverflow.com/help/someone-answers. Following these simple rules you increase your own reputation score and at the same time you keep us motivated to answer your questions :o) please consider! - Mikhail Berlyant

2 Answers

1
votes

See simplified example below 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 id, 10 val UNION ALL
  SELECT 2, 11 UNION ALL
  SELECT 10, 12
)
SELECT id, val 
FROM `project.dataset.table`
JOIN UNNEST(GENERATE_ARRAY(0,5,1)) key
ON id = key
ORDER BY id

Should be self-descriptive :o)

0
votes

You can give it an alias like as array_values in the select statement and in your query reference it using the alias name.