Database Reference
In-Depth Information
running.name AS app,
COUNT(1) AS cnt
FROM
FLATTEN([bigquery-e2e:ch10.sample_data], wireless)
WHERE id = 'U7nHcz-7bKTu'
AND wireless.connected
AND running.importance.level > 300
GROUP BY ssid, app
ORDER BY ssid, cnt DESC
Again the
JOIN
clauses drop away, but this time you must add a
FLATTEN
invocation around the source table. What if the
WHERE
clause involved three
independently repeating fields? The
FLATTEN
operator can be nested to
generate the cartesian product over multiple fields.
FLATTEN(FLATTEN(table), field1), field2)
Note that the first argument to flatten can also be a subquery. For example,
you could rewrite the query to a more efficient form that filters records
before flattening.
SELECT
wireless.ssid AS ssid,
running.name AS app,
COUNT(1) AS cnt
FROM FLATTEN(
SELECT *
FROM [bigquery-e2e:ch10.sample_data]
WHERE id = 'U7nHcz-7bKTu',
wireless)
WHERE running.importance.level > 300
AND wireless.connected
GROUP BY ssid, app
ORDER BY ssid, cnt DESC;
You may not observe an improvement in performance because the optimizer
could be performing the same transformation. One subtle point to pay
attention to is that it is OK to pass independently repeating fields through
a subquery. This is not a problem because the subquery just preserves
the repeated structure. Independently repeating fields are only a problem
