sql - 如何在大查询中使用某些键过滤结构

Question

有这样的数据：

Date, struct.key: struct.value
2019, {1: 'nice', 2: 'nice', 3: 'not nice'}
2020, {2: 'nice', 3: 'not nice'}

如何过滤所有出现 key=1 的结构？

预期输出：

Date, struct.key: struct.value
2019, {1: 'nice', 2: 'nice', 3: 'not nice'}

标签： sqlgoogle-bigquery

Answer 1

有了：

WITH `project.dataset.table` AS (
  SELECT 2019 AS date, (SELECT ARRAY_AGG(STRUCT(key, value))
  FROM (SELECT "1" as key,"nice" as value UNION ALL
  SELECT "2" as key,"nice" as value UNION ALL
  SELECT "3" as key,"not nice" as value)) AS structcolumn
  UNION ALL
  SELECT 2020 AS date, (SELECT ARRAY_AGG(STRUCT(key, value))
  FROM (SELECT "2" as key,"nice" as value UNION ALL
  SELECT "3" as key,"not nice")) AS structcolumn
)
SELECT 
  date,key,value
FROM `project.dataset.table`,UNNEST(structcolumn) AS STRUCTCOL
WHERE STRUCTCOL.key="1"

输出如下：

希望能帮助到你。