BigQuery nested repeated records throws an error

[sharno]

Describe the bug I have a bigquery schema that uses nested repeated records, any reference of a field in the nested record throws an error like this:

Error: Error: Cannot access field balance on a value with type ARRAY<STRUCT<price FLOAT64, balance FLOAT64, currency_iso STRING, ...>> at [5:41]

I think for this to be solved cube.js needs to use the UNNEST sql keyword to flatten the array of records: https://stackoverflow.com/questions/39109817/cannot-access-field-in-big-query-with-type-arraystructhitnumber-int64-time-in

The issue was mentioned at the end here, but it was closed so I opened a new one: #182

[paveltiunov]

@sharno Hey Mohamed! Thanks for posting this! For now you'd need to put this UNNEST expression in cube's sql in order to access nested fields. Even if schema generator creates these dimensions right now it can't generate right cube sql to access those.

As a first step I propose not to generate dimensions that require nested array field access. As a second step we can make generator smarter to support UNNEST sql generation.

[sharno]

Thanks Pavel, I fixed temporarily by flattening the tables ahead of time, although a smarter generator would definitely be pleasant

[ydennisy]

hey @paveltiunov I have been looking into Cube and one thing that worries me is running into any limitations with things such as BQ arrays etc, is that a possibility or due to the fact we can use raw SQL to create cubes that should not be the case?

[paveltiunov]

@ydennisy Hey Dennis! Yep. Exactly! As you can write SQL in cubes, it gives you an opportunity to flatten your arrays in whatever way you want.