Database Reference
In-Depth Information
if they explicitly appear in the query. Currently BigQuery automatically
flattens a single repeated field appearing in the outermost
SELECT
clause.
As a consequence, query results never have repeated fields. If more than
one independently repeating field appears in the outermost
SELECT
clause,
BigQuery will reject the query rather than generate the cartesian product of
the repeating fields. If this is what you require you can explicitly
FLATTEN
one of the repeating fields.
Repeated Field Functions
Almost all the functions in the query language operate the same way on
singleton and repeated fields. However, a couple of functions provided
specifically operate on or produce repeated fields.
•
NEST(
field
)
•
POSITION(
field
)
•
NTH(index,
field
)
•
LAST(
field
)
NEST
is an aggregation function that is roughly the inverse of FLATTEN.
It must be used as a column in the
SELECT
clause together with a
GROUP
BY
clause. Currently, it is not particularly useful because BigQuery flattens
all top-level query results, so it will undo the work of a top-level
NEST
expression. In addition, the ability to operate only on a single field
significantly limits its usefulness. In subqueries you will likely want to use
window functions instead of
NEST
, so we will not bother with an example of
its usage.
The rest of the functions take repeated fields as inputs. Keep in mind that
a field itself may not be repeated, but if any of the records in the path to
the field are repeated, then the field is treated as repeated. The functions
operate with respect to the scope specified by the
WITHIN
clause. For
example, in our application device log,
POSITION(running.name)
WITHIN RECORD
returns the index of the application in the list of
applications. As you have probably guessed,
POSITION
returns the position
of a value in a repeated field;
NTH
returns a value at a particular position in
the repeated field; and
LAST
returns the last element in the repeated field.
There is no
FIRST
because that is the same as
NTH(1,
field
)
. All the
