CODEWITHSUNDEEP
nestedgoogle-bigquerygoogle-cloud-datastore
rakensi
rakensi

Reputation: 1447

BigQuery record of repeated fields

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

Answers (1)

rakensi
rakensi

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

Related Questions