Bigquery:查询重复的key-value到单行

Question

我有一个类似下面的模式，其中我使用键-值对象重复字段：

metadata.date               STRING  NULLABLE    
metadata.properties         RECORD  REPEATED    
metadata.properties.key     STRING  NULLABLE    
metadata.properties.value   STRING  NULLABLE    

我有像{date："01-02"，properties：{key："foo"，value："bar"}，{key："bar"，value："baz"}，...

我想查询重复字段中的所有内容，作为单行的列。请注意，我们在properties重复字段中有100+条目。有没有一种方法可以在没有100+子查询的情况下实现这一点？我当前的查询是：

SELECT
    date,
    (SELECT value FROM unnest(metadata.properties) WHERE key='foo') as foo,
    (SELECT value FROM unnest(metadata.properties) WHERE key='bar') as bar,
    ...
FROM mytable

Answer 1

考虑以下内容-动态提取所有键并构建由execute immediate执行的所需查询

execute immediate (select '''select * except(row_id)
from (
  select to_json_string(t) row_id, metadata.date, key, value
  from `project.dataset.table` t, unnest(metadata.properties) property
)
pivot (max(value) for key in (''' || string_agg(distinct '"' || key || '"' order by '"' || key || '"') || '''))'''
from `project.dataset.table`, unnest(metadata.properties)
)

注意:很可能您可以分别排除/删除to_json_string(t) row_id和except(row_id)的使用，但我之所以将其包含在这里，是因为我不知道计划其余部分的所有细节，我希望确保行按原样保留，而不是意外分组