Bigquery: STRUCT (*) syntax

Question

How can I automatically apply STRUCT to all fields in a table without specifying them by name?

Example that does not work:

WITH data as (
 SELECT 'Alex' as name, 14 as age, 'something else 1' other_field
UNION ALL 
 SELECT 'Bert' as name, 14 as age, 'something else 2' other_field
UNION ALL 
 SELECT 'Chiara' as name, 13 as age, 'something else 3' other_field
)

SELECT AS STRUCT(SELECT * except (other_field) from data) as student_data

Returns: Error: Scalar subquery cannot have more than one column unless using SELECT AS STRUCT to build STRUCT values at [9:17]

This however works:

WITH data as (
 SELECT 'Alex' as name, 14 as age, 'something else 1' other_field
UNION ALL 
 SELECT 'Bert' as name, 14 as age, 'something else 2' other_field
UNION ALL 
 SELECT 'Chiara' as name, 13 as age, 'something else 3' other_field
)

SELECT STRUCT(name,age) as student_data

from data

The problem is that once I have 100 columns where only 5 don't belong, it makes me crazy to write them out. Is there a simpler way to use some version of Select * Except()?

Mikhail Berlyant · Accepted Answer · 2020-02-17T15:14:40.140

14

Below for BigQuery Standard SQL

#standardSQL
WITH data AS (
  SELECT 'Alex' AS name, 14 AS age, 'something else 1' other_field UNION ALL 
  SELECT 'Bert' AS name, 14 AS age, 'something else 2' other_field UNION ALL 
  SELECT 'Chiara' AS name, 13 AS age, 'something else 3' other_field
)
SELECT (
  SELECT AS STRUCT * EXCEPT(other_field)
  FROM UNNEST([t])
  ) AS student_data
FROM data t

with output

Row student_data.name   student_data.age     
1   Alex                14   
2   Bert                14   
3   Chiara              13

edited Feb 17 '20 at 15:14

answered Feb 17 '20 at 15:07

Mikhail Berlyant

165,386
8
154
230

1

This is great! Where an I find documentation on this notation for `UNNEST([t])`? – freekvd May 25 '20 at 11:39
Please note that you might need to wrap the SELECT AS STRUCT * EXCEPT(other_field) in (). The UNNEST([t]) part is not required. See https://stackoverflow.com/a/65302901/413531 for the full example. – Hirnhamster Dec 15 '20 at 09:06
@Hirnhamster - this is obviously wrong statement - just try both solutions and you will see why! :o) – Mikhail Berlyant Dec 15 '20 at 15:35

Hirnhamster · Answer 2 · 2020-12-16T09:46:32.370

4

As update to Mikhail Berlyant's answer https://stackoverflow.com/a/60265292/413531 because the comment section doesn't let me use proper formatting:

Please note that you might need to wrap the SELECT AS STRUCT * EXCEPT(other_field) in (). The UNNEST([t]) part is not required. I.e. this also works:

#standardSQL
WITH data AS (
  SELECT 'Alex' AS name, 14 AS age, 'something else 1' other_field UNION ALL 
  SELECT 'Bert' AS name, 14 AS age, 'something else 2' other_field UNION ALL 
  SELECT 'Chiara' AS name, 13 AS age, 'something else 3' other_field
)
SELECT
  (SELECT AS STRUCT data.* EXCEPT(other_field)) as student_data,
FROM data

edited Dec 16 '20 at 09:46

answered Dec 15 '20 at 09:08

Hirnhamster

7,101
8
43
73

see comments in answer you referenced :o) – Mikhail Berlyant Dec 15 '20 at 16:51
Not sure what you are referring to? Unless you mean the duplicate "name" - that slipped somehow in :) – Hirnhamster Dec 16 '20 at 09:45

score 2 · Answer 3 · answered Feb 17 '20 at 15:08

2

You need an expression for the AS STRUCT expr

SELECT AS STRUCT data.* except (other_field) from data

answered Feb 17 '20 at 15:08

Pentium10

204,586
122
423
502

Bigquery: STRUCT (*) syntax

3 Answers3