CODEWITHSUNDEEP
sqlarraysjsongoogle-bigquery
dorachan2010
dorachan2010

Reputation: 1091

How do I find elements in an array in BigQuery

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.200",
  "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

Upvotes: 27

Views: 96111

Answers (2)

Mosha Pasumansky
Mosha Pasumansky

Reputation: 14004

Mikhail's solution is good if it is guaranteed that there are no duplicate pairs in the cookie array. But if there could be duplicates, here is the alternative solution:

#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'), ('orange', 'orange'), ('orange', 'orange')]
)
SELECT ip
FROM yourTable t
WHERE (
  ('grape', 'purple')  IN UNNEST(t.cookie) AND
  ('orange', 'orange') IN UNNEST(t.cookie) )

Results in only

ip
-----------
192.168.1.1

Upvotes: 10

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

#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

Upvotes: 22

Related Questions