BigQuery SQL: select struct as single record, not array of records-CodePudding

I want to create a RECORD as the outcome of a select in BigQuery standard SQL.

If I run this SQL snippet,

WITH 
mock_data AS (
    SELECT 'foo1' as foo, 'bar1' as bar, 'bla1' as bla UNION ALL
    SELECT 'foo2' as foo, 'bar2' as bar, 'bla2' as bla
)
SELECT 
    *,
    STRUCT(
        m.foo as foo,
        m.bar as bar
    ) as foobar
FROM mock_data m

the output of foobar is an array of records, not a single record.

How could I have the foobar column be a single record and not an array of records? Thanks a lot in advance!

CodePudding user response：

It is a single record - not an array

You can clearly see this in JSON Tab

Also you can go to "Job Information" Tab

and click on "Temporary Table" to see the schema of output

In the BigQuery IDE that I am using - it is even more visible

CodePudding user response：

The UNNEST operator flattens arrays into rows, i.e. it breaks an array into several rows. STRUCT elements get broken down into multi-column records, which is what I think you require. Try something like this out:

SELECT
   *,
   UNNEST(
      STRUCT(
        m.foo as foo,
        m.bar as bar
      ) as foobar
   )