I am trying to model what I consider to be nested nested data (2 levels deep) from a big query table and am having trouble doing so or finding resources on how to do so. For context, this article https://help.looker.com/hc/en-us/articles/360023638874-Nested-Data-in-BigQuery-Repeated-Records- has been helpful. However, what if the data for citiesLived.numberOfYears was an array of numbers ([5,12,4,3])? How would I model for that?
I can currently access the first number of the array if I limit the query, other wise I get an error
explore: city_totals {
join: cities_lived {
sql: LEFT JOIN UNNEST(city_totals.cities_lived) as cities_lived ;;
relationship: one_to_many
}
}
view: cities_lived {
dimension: id{
primary_key: yes
type: string
sql: ${TABLE}.city_id ;;
hidden: yes
}
dimension: values_array {
type: number
#this works but only returns the first value of the array
sql: (SELECT * FROM UNNEST(${TABLE}.values) LIMIT 1) ;;
#this returns error: Query execution failed: - Scalar subquery produced more than one element
sql: (SELECT * FROM UNNEST(${TABLE}.values) ) ;;
}
}
Any help or direction is greatly appreciated. Thank you