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 of RECORD type and in NULLABLE mode, you can access the elements inside using dot notation like this: field.element_name:

SELECT
  u.id,
  u.name,
  u.user_info.occupation,
  u.user_info.age,
FROM users u

If your column is of RECORD type but is in REPEATED mode (essentially it's an array of objects), you need to unnest it before you can use dot notation:

// Flatten out all addresses that is stored in `users.addresses`
select
  u.id,
  u.name,
  unnested.street_address as street_address,
  unnested.postal_code as postal_code,
from users u
left join unnest(u.addresses) 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. Please refer to this blog post: How to extract nested JSON data in BigQuery

Using BigQuery at work?

Check out Holistics - modern BI tool for BigQuery.