sql - 查询 BigQuery 嵌套/重复字段

Question

我是 BigQuery(和 sql)的新手，正在尝试设置一个表，其中每行包含一个 message列(字符串)和 metadata列(重复)。我想使用元数据列，以便我可以查询与某些元数据参数匹配的消息。

例如，我的消息字符串是:
Hi honey, I'm home!
元数据列是描述消息的任意键/值对:
{"category": "personal",
"message_type": "salutation"}
这将允许我要求 bigquery:

返回所有带有 category 的消息的 personal (或 message_type 的 personal 等)

重申一下，插入的每个消息行的元数据键可能不同(例如，新行的元数据键可能是 favorite_color 而不是 category )。

这是我为了支持我想要的元数据灵活性而提出的模式:

[
{"name": "message", "type": "string", "mode": "required"}
{"name": "metadata", "type": "record", "mode": "repeated", "fields": [ {"name": "key", 
                                                                        "type": "string", 
                                                                        "mode": "required"},
                                                                       {"name": "value", 
                                                                        "type": "string", 
                                                                        "mode": "required"}]}
]

该模式期望每个元数据条目/行都包含一个名为 key 的列。和名为 value 的列.它似乎工作正常。

有趣的是，Bigquery 将我的消息行表示为两行(我猜这被称为“扁平化”？):

message                 | metadata_key    |    metadata_value
------------------------------------------------------------
Hi honey, I'm home!       category             personal
Hi honey, I'm home!       message_type         salutation

我想出了如何查询单个元数据值，例如 category = personal :

SELECT * FROM [table.test] 
WHERE
metadata.key="category" AND metadata.value="personal"

我得到了一行:

message                 | metadata_key    |    metadata_value
------------------------------------------------------------
Hi honey, I'm home!       category             personal

这很棒!但是，我不知道如何构建一个匹配多个元数据参数的更复杂的查询，例如:

获取所有带有 category 的消息的 personal和 message_type的 salutation
以下查询不返回任何内容:

SELECT * FROM [table.test] 
WHERE
metadata.key="category" AND metadata.value="personal" AND
metadata.key="message_type" AND metadata.value="salutation" 

我认为这是因为 bigquery 将我的单个消息行分解为两行(每个嵌套元数据行一个)。这显然意味着我的查询永远无法匹配单个行，因为我试图匹配两个不同的元数据行(即我要求 metadata.key 同时等于两个不同的值，而 metadata .value 同时等于不同的值)。所以我对如何适本地构建我的查询感到有些困惑。

理想情况下，我希望 BigQuery 返回 单例行(我开始的原始示例行)，因为它满足我的查询的两个元数据要求。

我假设我需要 GROUP BY、FLATTEN、WITHIN、unflatten 等的组合。对于这个 sql/bigquery 新手来说，可能性/结构的组合有点令人生畏。任何帮助表示赞赏:)

Answer 1

尝试以下 - 只是选项之一
它总结了每行中有多少“匹配项，如果全部(在这种情况下 - 2)它输出它。非常简单

SELECT *, 
SUM((metadata.key="category" AND metadata.value="personal")
 OR (metadata.key="message_type" AND metadata.value="salutation")
) WITHIN RECORD AS check,
FROM [table.test] 
HAVING check = 2

更清洁的版本如下

SELECT * 
FROM [table.test]
OMIT RECORD IF 
SUM((metadata.key="category" AND metadata.value="personal")
 OR (metadata.key="message_type" AND metadata.value="salutation")
) != 2