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

Question

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!

Mikhail Berlyant · Accepted Answer · 2022-03-31 16:19:27Z

2

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

edited Mar 31, 2022 at 16:19

answered Mar 31, 2022 at 16:14

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

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

1 Comment

david fdez Over a year ago

Interesting! Thanks a lot Michail I got confused by how it is displayed in the results, with "row 1" as if there could be various rows. Solved!

Blend3rman · Accepted Answer · 2022-03-31 09:38:25Z

1

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
   )

answered Mar 31, 2022 at 9:38

Blend3rman

1851 silver badge14 bronze badges

1 Comment

david fdez Over a year ago

Thanks a lot @Blend3rman! Your code seems to be wrong syntactically, if I try the following adaptation it complains about UNNEST needing to be done on an array and not a single struct:

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      *,    (SELECT * FROM UNNEST(STRUCT(         m.foo as foo,         m.bar as bar     ))) as foobar FROM mock_data m

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related