Querying multiple repeated fields in BigQuery

Question

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

Answer 1

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)
    )
  )

Answer 2

If You don't need to do anything specific inside the inner select, You can just use

(FLATTEN(FLATTEN(table, a1), a2))

Answer 3

Now that BigQuery has moved to Standard SQL, using FLATTEN doesn't work. However Google has documented how to migrate. This solution worked for me, although there are several other ways to do it:

SELECT
  flattened_field_1,
  flattened_field_2

FROM my_dataset.my_table
LEFT JOIN UNNEST(repeated_field_1) AS flattened_field_1
LEFT JOIN UNNEST(repeated_field_2) AS flattened_field_2
# ...etc