sql - 如何从具有字典列表的表中查询，仅针对某些键 (BigQuery) SQL

Question

我在 Google BigQuery 上有一个表，其中每一行都有一个名为“customized_field”的字段，它是一个包含 25 个字典列表的字典（每个字典只有 1 个键/值对）。每行都有一个相同的字典（具有相同的“id”名称），只是“值”中的数量不同。字典看起来像这样：

[
  {
    "customized_field": [
      {
        "data": {
          "id": "Bob",
          "value": 3
        }
      },
      {
        "data": {
          "id": "Jim",
          "value": 4
        }
      },
      {
        "data": {
          "id": "Mary",
          "value": 2
        }
      },
      etc etc... (22 more)
   }
]

我想创建一个创建两列（特别是“Bob”和“Mary”）的表，其中两列中的每一列的值都是字典中的“值”。所以表格看起来像这样：

鲍勃	玛丽
3	2
4（假设这是下一行的值）	5（假设这是下一行的值）

我的 SQL 脚本如下所示：

SELECT
CASE when h.data.id = "Bob" then h.value.value end Bob,
CASE when h.data.id = "Mary" then h.value.value end Mary
FROM `my_database`, UNNEST(`my_database `. customized_field) AS h

然而，这给了我一个看起来我想要的表，但它创建了我需要的行数的 25 倍（我相信它因为 25 个键值对而重复，并且当我执行“unnest”时，它基本上会创建更多行）。我怎样才能得到它，这样它就不会重复？

我的查询给出了这样的表：

鲍勃	玛丽
3	2
4	5
3	2
4	5
3	2
4	5

等等等等。

标签： sqlgoogle-bigqueryunnest

Answer 1

考虑以下方法

select * except(key) from (
  select h.data.id, h.data.value, to_json_string(t) key
  from `my_database` t, unnest(t.customized_field) AS h
)
pivot (max(value) for id in ('Bob', 'Mary'))     

输出将如下所示