Querying multiple repeated fields in BigQuery

google-bigquery

Solution

You can use a nested subselect inside the `FLATTEN`. It requires an extra paren around the select statement. (the syntax is kind of ugly, unfortunately). e.g.

SELECT
  ...
FROM (
    FLATTEN((
      SELECT
        ...
      FROM (
          FLATTEN((
            SELECT
              ...
            FROM
              table),
            f1)
          )
        ),
      f2)
    )
  )

Problem

I have a schema that contains multiple repeated fields which are not nested. I'm trying to query the cross product, but I get an error: "Cannot query the cross product of repeated fields..." If I query only 2 repeated fields, I can flatten one of them. Yet, I'm interested in querying more that 2 repeated fields, and I can't understand how FLATTEN syntax supports this. For example, say the table structure is: a1, record (repeated) : a1.b1, integer a2, record (repeated) : a2.b1, integer a3, record (repeated) : a3.b1, integer I want to query: select (*) from tab

Original source