Does BigQuery have the concept of a ROW
, for example, similar to MySQL or Postgres or Oracle or Snowflake? I know it sort of implicitly uses it when doing an INSERT ... VALUES (...)
, for example:
INSERT dataset.Inventory (product, quantity) VALUES('top load washer', 10), ('front load washer', 20)
Each of the values would be implicitly be a ROW
type of the Inventory
table, but is this construction allowed elsewhere in BigQuery? Or is this a feature that doesn’t exist in BQ?
Advertisement
Answer
I think below is a simplest / naïve example of such constructor in BigQuery
with t1 as ( select 'top load washer' product, 10 quantity, 'a' type, 'x' category union all select 'front load washer', 20, 'b', 'y' ), t2 as ( select 1 id, 'a' code, 'x' value union all select 2, 'd', 'z' ) select * from t1 where (type, category) = (select as struct code, value from t2 where id = 1)
Besides using in simple queries, it can also be use in BQ scripts – for example (another simplistic example)
declare type, category string; create temp table t2 as ( select 1 id, 'a' code, 'x' value union all select 2, 'd', 'z' ); set (type, category) = (select as struct code, value from t2 where id = 1);