title: How do I find elements in an array in BigQuery
body: <p>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.</p>
  
  <pre><code>{
    "ip": "192.168.1.1",
    "cookie" [
      {
        "key": "apple",
        "value: "red"
      },
      {
        "key": "orange",
        "value: "orange"
      },
      {
        "key": "grape",
        "value: "purple"
      }
    ]
  }
  </code></pre>
  
  <p>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.</p>
  
  <pre><code>SELECT ip
  FROM table t, t.cookie c
  WHERE (c.key = "grape" AND c.value ="purple") AND (c.key = "orange" AND c.value ="orange")
  </code></pre>
  
  <p><a href="https://stackoverflow.com/questions/38277110/bigquery-repeated-field-contains">This link</a> is really close to what I want to do, except they are using <code>legacy SQL</code> and not <code>standardSQL</code></p>