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()?
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