BigQuery except double nested column

Question

Hi need to exclude the column hits.latencyTracking.userTimingLabel from my table and select all the other columns. How can I do this in Standard Sql? I found already a question on a similar problem (BigQuery select * except nested column) but in my case I have a double nested column and I am not able do adapt the code. thanks

Answer 1

If hits is an array and latencyTracking is a struct, then:

SELECT ARRAY(SELECT (SELECT AS STRUCT latencyTracking.* EXCEPT(userTimingLabel)) AS latencyTracking FROM UNNEST(hits)) AS hits
FROM YourTable;

Answer 2

Below are my few cents on this:
I think it answers your exact question / case

#standardSQL
SELECT * REPLACE(
  ARRAY(
    SELECT AS STRUCT hit.* 
      REPLACE((SELECT AS STRUCT latencyTracking.* EXCEPT(userTimingLabel)) AS latencyTracking)
    FROM UNNEST(hits) AS hit
  ) AS hits)
FROM `yourProject.yourDataset.yourTable`    

I tested it on public table as below:

#standardSQL
SELECT * REPLACE(
  ARRAY(
    SELECT AS STRUCT hit.* 
      REPLACE((SELECT AS STRUCT page.* EXCEPT(hostname)) AS page)
    FROM UNNEST(hits) AS hit
  ) AS hits)
FROM `google.com:analytics-bigquery.LondonCycleHelmet.ga_sessions_20130910`

Answer 3

The difference between select * except nested column and your question, is due to the fact that hits is an ARRAY. Here is how you can exclude columns from array of structs:

SELECT * REPLACE 
 (ARRAY(SELECT AS STRUCT * EXCEPT(page) FROM UNNEST(hits) h) AS hits)
FROM `google.com:analytics-bigquery.LondonCycleHelmet.ga_sessions_20130910`