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

score 14 · Accepted Answer · edited Jul 03 '15 at 14:19

14

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

edited Jul 03 '15 at 14:19

Zoltán

21,321
14
93
134

answered Jul 11 '13 at 18:29

Jordan Tigani

26,089
4
60
63

Works like a charm! I would like to specify the fields to select only once, in the most inner select, and write select * on all other nested selects. But then I can't specify an order by clause, since order by requires the field to appear in the select fields. – Lior Jul 12 '13 at 13:17
Nice job on the accepted answer. However, probably should be updated: https://cloud.google.com/bigquery/docs/reference/standard-sql/migrating-from-legacy-sql. – Chris Aug 30 '18 at 18:20

score 7 · Answer 2 · answered Aug 22 '13 at 12:17

7

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

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

answered Aug 22 '13 at 12:17

AndyTheEntity

3,396
1
22
19

score 2 · Answer 3 · answered Aug 30 '18 at 18:19

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

Querying multiple repeated fields in BigQuery

3 Answers3

Linked