Select Values in BigQuery?

Question

Is it possible to do something like the following in BigQuery to quickly generate sample input?

SELECT * FROM values ('david',10), ('tom',20)

Or does it only accept the verbose SELECT ... UNION ALL ... format:

select 'david' name, 10 age union all select 'tom', 20;

Mikhail Berlyant · Accepted Answer · 2021-10-25 22:02:28Z

21

Consider below as an option

SELECT * FROM UNNEST([
    STRUCT('david' as name,10 as age), 
    ('tom', 20),
    ('jon', 30)
  ])

OR

SELECT * FROM UNNEST([STRUCT<name STRING, age INT64>
    ('david',10), ('tom', 20), ('jon', 30)
  ])

both give you quick dummy data to play with

answered Oct 25, 2021 at 22:02

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

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

2 Comments

cool, yea the second approach is easy enough to remember I think I'll use that -- thanks!

This is super helpful but note to reader: it doesn't work if you just have a single column. Add a second dummy column and you'll be fine. :)