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

冯明雁

我正在尝试搜索在数组中具有某些键值对的行。我的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")
这个链接真的很接近我想要做的，除了他们正在使用legacy SQL而不是正在使用standardSQL

解决方案:


            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为>=1inWHERE子句

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

冯明雁 LV1