Reputation: 1447
I have a Bigquery table with (what is conceptually) a field containing repeated record.
However, this field is stored as a record of repeated fields. This is either caused by the export from AppEngine DataStore (using Mache), or by the representation of the data (using Objectify 3); I don't know.
So what I have is a field (exercises
) that looks like this:
exercises RECORD NULLABLE exercises
exercises.id INTEGER REPEATED id
exercises.weight FLOAT REPEATED weight
exercises.duration STRING REPEATED duration
instead of
exercises RECORD REPEATED exercises
exercises.id INTEGER NULLABLE id
exercises.weight FLOAT NULLABLE weight
exercises.duration STRING NULLABLE duration
The latter can be queried easily using FLATTEN
(legacy SQL) or UNNEST
(standard SQL). However, with the schema I have now, I seem to be stuck.
I guess I would have to to transpose the exercises
field in some way, from a records of arrays to an array of records.
The sub-fields of exercises
always have the same length, so that should not be a problem.
How can I query and select this field?
Upvotes: 0
Views: 1962
Reputation: 1447
I have tried UNNEST WITH OFFSET
, as suggested here:
SELECT
exerciseId, exofs,
exercises.weight[OFFSET(exofs)] AS exerciseWeight,
exercises.duration[OFFSET(exofs)] AS exerciseDuration
FROM Session, UNNEST(exercises.id) AS exerciseId WITH OFFSET exofs
This works! This feature is only available in standard SQL. The FLATTEN
in legacy SQL does not support WITH OFFSET
.
Upvotes: 1