KNOWLEDGE BASE

Using BigQuery Standard SQL and Nested Fields


Published: 20 Jan 2017
Last Modified Date: 21 Jul 2020

Issue

When connecting to a BigQuery table that contains repeated RECORD fields, the following error may occur:
 
The Google BigQuery service was unable to compile the query.
Cannot access field allocation_method on a value with type ARRAY<STRUCT<value_type STRING, value STRING, allocation_method STRING, ...>> at [1:
<number>]

Environment

  • Tableau Desktop
  • Google BigQuery

Resolution

As a possible workaround, the FLATTEN() function can be used in Google BigQuery to expand the nested fields into flat tables. Those tables, as saved views, can then be connected with Tableau Desktop.

Note: While we make every effort to keep references to third-party content accurate, the information we provide here might change without notice.

Cause

The ability to query repeated RECORD fields in a Google BigQuery connection is currently not built into the product.
Did this article resolve the issue?