Google DataStudio on BigQuery Data, How to display Struct of Arrays

user_7225791 0
Viewed 1618

I'm trying to display a table in DataStudio plugged on a BigQuery Table. Where I have a String field, and a Struct of 2 Arrays. This is where my issue is.

Table Schema

When I want to include both of my arrays from the struct, the table kind of time out and shows a connection error. Whereas when I try to include on of them independently there are no issues.

This kind of struct is not supported in DataStudio? Or am I doing something wrong? Thank you.

google-data-studio google-bigquery
1 Answers

It doesn't support it. You have to transform it on go in SELECT clause.

If you want to concatenate all strings from repeated string field you can use ARRAY_TO_STRING:

ARRAY_TO_STRING(recos.reco_sku)

or for integers, you have to cast them into a string and then concatenate them 

ARRAY_TO_STRING(
  ARRAY(
    SELECT 
      CAST(i AS STRING) 
    FROM 
      UNNEST(recos.nb_asso) AS i WITH OFFSET o 
    ORDER BY 
      o
  )
)

Otherwise, you can explode your array with LEFT/CROSS JOIN + UNNEST and make rows flat for each array entry.

user_21229900
Related