sql - 如何在 BigQuery 中查找数组中的元素

Question

我正在尝试在数组中搜索具有某些键值对的行。我的 BigQuery 表中的一行看起来像这样。

{
  "ip": "192.168.1.1",
  "cookie" [
    {
      "key": "apple",
      "value: "red"
    },
    {
      "key": "orange",
      "value: "orange"
    },
    {
      "key": "grape",
      "value: "purple"
    }
  ]
}

我想过使用如下所示的隐式 UNNEST 或 CROSS JOIN，但它不起作用，因为取消嵌套只会创建多个不同的行。

SELECT ip
FROM table t, t.cookie c
WHERE (c.key = "grape" AND c.value ="purple") AND (c.key = "orange" AND c.value ="orange")

This link非常接近我想要做的，除了他们使用的是 legacy SQL而不是 standardSQL

Answer 1

#standardSQL
SELECT ip
FROM yourTable 
WHERE (
  SELECT COUNT(1) 
  FROM UNNEST(cookie) AS pair 
  WHERE pair IN (('grape', 'purple'),  ('orange', 'orange'))
) >= 2

您可以使用以下虚拟数据对其进行测试

#standardSQL
WITH yourTable AS (
  SELECT '192.168.1.1' AS ip, [('apple', 'red'), ('orange', 'orange'), ('grape', 'purple')] AS cookie UNION ALL
  SELECT '192.168.1.2', [('abc', 'xyz')]
)
SELECT ip
FROM yourTable 
WHERE (
  SELECT COUNT(1) 
  FROM UNNEST(cookie) AS pair 
  WHERE pair IN (('grape', 'purple'),  ('orange', 'orange'))
) >= 2

如果您需要输出 ip 如果数组中至少有一对 - 您需要更改 >= 2至 >=1在 WHERE条款