BigQuery select * except two columns

Question

I would like to select everything from the public BigQuery github_repos dataset except from two records: author.name AND difference.old_mode. Based on a similar question I asked, I think I want to run a query similar to

#standardSQL
SELECT * REPLACE ((SELECT AS STRUCT author.* EXCEPT (name)) AS author), 
REPLACE ((SELECT AS STRUCT difference.* EXCEPT (old_mode)) AS difference)
FROM `bigquery-public-data.github_repos.commits`
LIMIT 1000;

If I run the author exclusion, it works well:

#standardSQL
SELECT * REPLACE ((SELECT AS STRUCT author.* EXCEPT (name)) AS author)
FROM `bigquery-public-data.github_repos.commits`
LIMIT 1000;

However, the difference exclusion has an error:

#standardSQL
SELECT * REPLACE ((SELECT AS STRUCT difference.* EXCEPT (old_mode)) AS difference)
FROM `bigquery-public-data.github_repos.commits`
LIMIT 1000;

Error:

Dot-star is not supported for type ARRAY<STRUCT<old_mode INT64, new_mode INT64, old_path STRING, ...>> at [2:41]

Thank you.

Update Not a duplicate of SQL server question.

Possible duplicate of [SQL exclude a column using SELECT \* \[except columnA\] FROM tableA?](http://stackoverflow.com/questions/729197/sql-exclude-a-column-using-select-except-columna-from-tablea) — Sam W, Dec 07 '16 at 16:01
Thank you, but that MS SQL server link doesn't really help me here unfortunately. — Spikey, Dec 07 '16 at 16:30

score 12 · Accepted Answer · answered Dec 07 '16 at 17:37

As a self-contained example, consider this query:

WITH T AS (
  SELECT 10 AS a, ARRAY<STRUCT<x INT64, y STRING, z BOOL>>[
      (1, 'foo', true)] AS arr UNION ALL
  SELECT 11, ARRAY<STRUCT<x INT64, y STRING, z BOOL>>[
      (2, 'bar', false), (3, 'baz', true)]
)
SELECT * FROM T;

It returns a column a of type INT64 and a column arr of type ARRAY<STRUCT<x INT64, y STRING, z BOOL>>. If you wanted to return a modification of arr where the struct inside the array omits y, you could use a combination of SELECT * REPLACE and SELECT * EXCEPT:

WITH T AS (
  SELECT 10 AS a, ARRAY<STRUCT<x INT64, y STRING, z BOOL>>[
      (1, 'foo', true)] AS arr UNION ALL
  SELECT 11, ARRAY<STRUCT<x INT64, y STRING, z BOOL>>[
      (2, 'bar', false), (3, 'baz', true)]
)
SELECT * REPLACE(ARRAY(SELECT AS STRUCT * EXCEPT (y) FROM UNNEST(arr)) AS arr)
FROM T;

The idea is to replace the original array with a new one, and we use an ARRAY subquery with SELECT AS STRUCT and * EXCEPT to reconstruct the array with struct elements whose fields don't include y.

Going back to the query in the question, you can apply the same idea to difference and old_mode:

SELECT * REPLACE (
  ARRAY(SELECT AS STRUCT * EXCEPT (old_mode) FROM UNNEST(difference)) AS difference
)
FROM `bigquery-public-data.github_repos.commits`
LIMIT 1000;

The query result contains a difference array whose struct doesn't include the old_mode field.

You're awesome; thank you so much. For anyone referencing this in the future, to complete the example with my question, all that needs to be done is the other replace query line I had working added to the replace function Elliott had above. This successfully removes both fields. — Spikey, Dec 07 '16 at 18:00
@Elliott What if my table structure is the one below. How should I change my query. WITH T AS ( SELECT 10 AS a, STRUCT (1, 'foo', true) AS arr UNION ALL SELECT 11, STRUCT (2, 'bar', false) ) — phaigeim, Apr 04 '19 at 10:45

BigQuery select * except two columns

1 Answers1

Linked