BigQuery 选择 * 两列除外

我想从公共 BigQuery github_repos 数据集中选择除以下两条记录之外的所有内容：author.nameAND差异.old_mode。根据我问的类似问题，我想我想运行类似于

#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;

如果我运行作者排除，效果很好：

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

然而，差分排除有一个错误：

#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]

谢谢。

Update不是重复的SQL服务器 https://stackoverflow.com/questions/729197/sql-exclude-a-column-using-select-except-columna-from-tablea问题。

作为一个独立的示例，请考虑以下查询：

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;

它返回一列a类型的INT64和一列arr类型的ARRAY<STRUCT<x INT64, y STRING, z BOOL>>。如果你想返回修改arr数组内的结构体省略的地方y，你可以使用以下组合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;

我们的想法是用新数组替换原始数组，我们使用ARRAY子查询与SELECT AS STRUCT and * EXCEPT使用字段不包含的结构元素重建数组y.

回到问题中的查询，您可以将相同的想法应用于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;

查询结果包含一个difference其结构不包含的数组old_mode field.