你如何比较BigQuery中的两个数组？ [英] How do you compare two arrays in BigQuery?

问题描述

我试图加入两个表，每个表都有一个如下的数组列：

  SELECT a.id，b。值
从内部连接b 
在a.array中b.array 
  

或

  SELECT a.id，b.value 
 FROM INNER JOIN b 
 ON UNNEST（ a.array）IN UNNEST（b.array）
  

根据这个SO问题，postgres有像< @ 和> @ 这样的运算符，比较两者是否是其他数组的子集（
postgres文档页），但BigQuery只允许将数组的元素与其他数组进行比较，如下所示：

  a.arrayelement IN UNNEST （b.array）
  

是否可以在BigQuery中完成？

编辑

这是schema我正在使用

  WITH b AS（
 {ip：192.168.1.1，
cookie：[
 {key：apple，
value：red
}，
 {key：peach，
value：pink
}，
 {key：orange，
value：orange
} 
] 
 
 {ip：192.168.1.2，
cookie：[
 {key：apple，
value：red 
，
 {key：orange，
value：orange
} 
] 
} 
） ，
 with a（
 {id：12345，
cookie：[
 {key：peach，
value ：粉红
} 
] 
} 
，{id：67890，
cookie：[
 {key ：apple，
value：red
}，
 {key：orange，
value：orange
}， 
 
] 
} 
）
  

我预计输出如下：

  ip，id 
 192.168.1.1，67890 
 192.168.1.2,67890 
 192.168.1.2，12345 
  

它是以下SO的延续，
如何在BigQuery中查找数组中的元素。
我尝试使用子查询来比较其中一个数组的单个元素，但BigQuery返回一个错误，指出我有太多子查询

），
b AS（
 SELECT 11 AS值，[1,2,3,4] AS b_arr UNION ALL 
 SELECT 12，[1,3,5， 6] 
）
 SELECT a.id，b.value 
 FROM a，b，UNNEST（[（SELECT ARRAY_LENGTH（a.a_arr） -  COUNT（1）
 FROM UNNEST （a.a_arr）AS x 
 JOIN UNNEST（b.b_arr）AS y 
 ON x = y）]）AS z 
 WHERE z = 0 
  
 
 >  SELECT a.id，b.value 
 FROM INNER JOIN b 
 ON a.array IN b.array 
  
让我知道你是否希望我将它应用于你的例子 - 或者你会先尝试一下：o） 
I am trying to join two tables that each have an array column like the following
SELECT a.id, b.value
FROM a INNER JOIN b
ON a.array IN b.array
or
SELECT a.id, b.value
FROM a INNER JOIN b
ON UNNEST(a.array) IN UNNEST(b.array)
According to this SO question, postgres has operators like <@ and >@ that compares if either is a subset of the other array (
postgres doc page) but BigQuery only allows an element of the array to be compared with the other array like the following
a.arrayelement IN UNNEST(b.array)
Can it be done in BigQuery?

edit

This is the schema I am working with
WITH b AS (
    {  "ip": "192.168.1.1",
      "cookie": [
        { "key": "apple",
          "value: "red"
        },
        { "key": "peach",
          "value: "pink"
        },
        { "key": "orange",
          "value: "orange"
        }
      ]
    }
    ,{  "ip": "192.168.1.2",
      "cookie": [
        { "key": "apple",
          "value: "red"
        },
        { "key": "orange",
          "value: "orange"
        }
      ]
    }
   ),
WITH a AS (
    {  "id": "12345",
      "cookie": [
        { "key": "peach",
          "value: "pink"
        }
      ]
    }
    ,{  "id": "67890",
      "cookie": [
        { "key": "apple",
          "value: "red"
        },
        { "key": "orange",
          "value: "orange"
        },

      ]
     }
)
I am expecting an output like the following
ip, id
192.168.1.1, 67890 
192.168.1.2, 67890 
192.168.1.2, 12345
It is a continuation of the following SO,
How do I find elements in an array in BigQuery .
I tried using subqueries to compare a single element of one of the array, but BigQuery returns an error saying that I have "too many subqueries"
解决方案
Try below example (BigQuery Standard SQL)   
#standardSQL
WITH a AS (
  SELECT 1 AS id, [2,4] AS a_arr UNION ALL
  SELECT 2, [3,5]
),
b AS (
  SELECT 11 AS value, [1,2,3,4] AS b_arr UNION ALL
  SELECT 12, [1,3,5,6]
)
SELECT a.id, b.value
FROM a , b , UNNEST([(SELECT ARRAY_LENGTH(a.a_arr) - COUNT(1) 
                      FROM UNNEST(a.a_arr) AS x 
                      JOIN UNNEST(b.b_arr)  AS y 
                      ON x = y)]) AS z
WHERE z = 0
it mimics below pseudo-code:   
SELECT a.id, b.value
FROM a INNER JOIN b
ON a.array IN b.array  
Let me know if you want me to apply this to your example - or you will try by yourself first  :o)

                        
这篇关于你如何比较BigQuery中的两个数组？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！