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](https://stackoverflow.com/a/1647426/6719378), postgres has operators like *<@* and *>@* that compares if either is a subset of the other array (
[postgres doc page](https://www.postgresql.org/docs/8.2/static/functions-array.html)) 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](https://stackoverflow.com/questions/42989922/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"*