Handle RECORD type in BigQuery

Question

I have connected my BigQuery data source in which most of the tables we have nested field structure of type record. I'm not able to see/use nested fields to create the visualizations.

Answer

• If your column is in RECORD type and in NULLABLE mode, you can access the elements inside using dot notation like this: field.element_name

• If your column is of RECORD type but is in REPEATED mode, you need to unnest it before you can use dot notation:

select
unnested.element_1
, unnested.element_2
from base_table t
left join unnest(t.repeated_field_name) as unnested

• If your field is of STRING type but looks like a JSON, further steps need to be done before accessing all the elements

For more details, please refer to this blog post: How to extract nested JSON data in BigQuery