title: How do you compare two arrays in BigQuery?
body: <p>I am trying to join two tables that each have an array column like the following</p>
  
  <pre><code>SELECT a.id, b.value
  FROM a INNER JOIN b
  ON a.array IN b.array
  </code></pre>
  
  <p>or</p>
  
  <pre><code>SELECT a.id, b.value
  FROM a INNER JOIN b
  ON UNNEST(a.array) IN UNNEST(b.array)
  </code></pre>
  
  <p>According to <a href="https://stackoverflow.com/a/1647426/6719378">this SO question</a>, postgres has operators like <em>&lt;@</em> and <em>>@</em> that compares if either is a subset of the other array (
  <a href="https://www.postgresql.org/docs/8.2/static/functions-array.html" rel="noreferrer">postgres doc page</a>) but BigQuery only allows an element of the array to be compared with the other array like the following</p>
  
  <pre><code>a.arrayelement IN UNNEST(b.array)
  </code></pre>
  
  <p>Can it be done in BigQuery?</p>
  
  <p><strong>edit</strong></p>
  
  <p>This is the schema I am working with</p>
  
  <pre><code>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"
          },
  
        ]
       }
  )
  </code></pre>
  
  <p>I am expecting an output like the following</p>
  
  <pre><code>ip, id
  192.168.1.1, 67890 
  192.168.1.2, 67890 
  192.168.1.2, 12345
  </code></pre>
  
  <p>It is a continuation of the following SO,
  <a href="https://stackoverflow.com/questions/42989922/how-do-i-find-elements-in-an-array-in-bigquery">How do I find elements in an array in BigQuery</a> .
  I tried using subqueries to compare a single element of one of the array, but BigQuery returns an error saying that I have <em>"too many subqueries"</em></p>