如何在BigQuery中查找数组中的元素 [英] How do I find elements in an array in BigQuery

问题描述

我试图搜索一个在数组中具有特定键值对的行。

  {
ip：192.168.1.1 ，
cookie[
 {
key：apple，
value：red
}，
 {
key：orange，
value：orange
}，
 {
key：grape，
value：紫色
} 
] 
} 
  

隐式UNNEST或CROSS JOIN如下所示，但它不起作用，因为它将会创建多个不同的行。

  SELECT ip 
 FROM table t，t.cookie c 
 WHERE（c.key =grapeAND c.value =purple）AND（c.key =orangeAND c.value =橙色）
  

这个链接非常接近我想要做的，除了它们使用传统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 
 WHERE pair IN（（'grape'，' （'orange'，'orange'））
）> = 2 
  

如果你需要输出ip，如果至少有一对在数组中 - 你需要将> = 2 更改为> = 1 在中有E 子句

I am trying to search for a row that has certain key value pairs in an array. A row in my BigQuery table would look something like this.

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

I thought about using implicit UNNEST or CROSS JOIN like the following, but it didn't work because unnesting it would just create multiple different rows.

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 is really close to what I want to do, except they are using legacy SQL and not standardSQL

解决方案

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

you can test it with below dummy data

#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

In case if you need output ip if at least one pair is in array - you need to change >= 2 to >=1 in WHERE clause

这篇关于如何在BigQuery中查找数组中的元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！