BigQuery选择*除两列外

Question

我想从公共BigQuery github_repos数据集中选择所有内容，但要从以下两个记录中选择：author.name 和
difference.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;

错误：

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

谢谢你。

Answer 1

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

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;

它返回atypeINT64的列arr和type的列ARRAY<STRUCT<x INT64, y STRING, z BOOL>>。如果要返回arr对数组内部结构省略位置的修改，则y可以使用SELECT * REPLACEand的组合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并使用* EXCEPT其字段不包括的struct元素重构该数组y。

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