BigQuery add new columns into nested STRUCT in subquery

Question

I have the following table that contains nested STRUCTs, and in a subquery, I am trying to add additional columns at the struct level. I've created a reproducible example of my efforts thus far:

WITH wide_stats AS (
  (
    SELECT 
      'joe' name, 'bills' team,
      struct(struct(7 as fga, 5 as fgm) as o, struct(8 as fga, 3 as fgm) as d) as t1,
      struct(struct(3 as fga, 4 as fgm) as o, struct(9 as fga, 2 as fgm) as d) as t2
  ) UNION ALL (
    SELECT 'nick' name, 'jets' team,
      struct(struct(12 as fga, 7 as fgm) as o, struct(13 as fga, 7 as fgm) as d) as t1,
      struct(struct(15 as fga, 7 as fgm) as o, struct(22 as fga, 7 as fgm) as d) as t2
  )
)

SELECT 
  *,
--   safe_divide(wide_stats.t1.o.fgm, wide_stats.t1.o.fga) as fg_pct,
  safe_divide(wide_stats.t1.o.fgm, wide_stats.t1.o.fga) as wide_stats.t1.o.fg_pct  

FROM wide_stats

The current code throws an error Syntax error: Unexpected "." at [18:70] at line 18 (with the safe_divide). If I switch in line 17 / out line 18, the code works, but then fg_pct is not in the t1.o struct, where i'd like it to be.

Is there any way to add columns into nested structs in subqueries like this?

Mikhail Berlyant Mikhail Berlyant · Accepted Answer · 2019-11-10T05:02:39

Below is for BigQuery Standard SQL

#standardSQL
WITH wide_stats AS (
  SELECT 'joe' name, 'bills' team,
    STRUCT(STRUCT(7 AS fga, 5 AS fgm) AS o, STRUCT(8 AS fga, 3 AS fgm) AS d) AS t1,
    STRUCT(STRUCT(3 AS fga, 4 AS fgm) AS o, STRUCT(9 AS fga, 2 AS fgm) AS d) AS t2 UNION ALL 
  SELECT 'nick' name, 'jets' team,
    STRUCT(STRUCT(12 AS fga, 7 AS fgm) AS o, STRUCT(13 AS fga, 7 AS fgm) AS d) AS t1,
    STRUCT(STRUCT(15 AS fga, 7 AS fgm) AS o, STRUCT(22 AS fga, 7 AS fgm) AS d) AS t2
)
SELECT * REPLACE (
  (SELECT AS STRUCT t1.* REPLACE (
    (SELECT AS STRUCT t1.o.*, SAFE_DIVIDE(wide_stats.t1.o.fgm, wide_stats.t1.o.fga) AS fg_pct ) 
    AS o))
  AS t1)
FROM wide_stats

with result

Row name    team    t1.o.fga    t1.o.fgm    t1.o.fg_pct         t1.d.fga    t1.d.fgm    t2.o.fga    t2.o.fgm    t2.d.fga    t2.d.fgm     
1   joe     bills   7           5           0.7142857142857143  8           3           3           4           9           2    
2   nick    jets    12          7           0.5833333333333334  13          7           15          7           22          7

BigQuery add new columns into nested STRUCT in subquery

1 Answers