Possible to create a multi-column table from unnest?-CodePudding

Is it possible to do something like the following in BigQuery?

SELECT country, id FROM 
    -- possible to in-line this, with an array or struct even?
    [['us', 1], ['ca',2]] AS country (country, id)

Or perhaps there's a better way to do it? Currently the way I'm doing it is the following, but I want to see if I can inline it into the FROM instead:

WITH country AS (
    SELECT 'us' AS country, 1 as id UNION ALL
    SELECT 'ca', 2
)

CodePudding user response：

Another possible option would be to make it ARRAY<STRUCT<>> type. Consider below:

SELECT * FROM UNNEST([
  STRUCT('us' AS country, 1 AS id),
  STRUCT('ca',2)
]);

CodePudding user response：

One way to do this would be a cross-join on the UNNEST and then filtering by the offset of the array. Note that arrays in this case are columns and not rows:

SELECT country, id FROM
  UNNEST(["us", "ca"]) country WITH OFFSET o0, 
  UNNEST([1,2])             id WITH OFFSET o1 
WHERE o0=o1 -- normalize all unnests