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
This is an interesting situation. Am I assuming correctly your data has array of arrays and that’s why you have two levels?
I don’t have a straight answer right now but I will definitely have a look at what’s possible, as this seems like something that can be quite useful
Hi Dawid, your assumption is correct. The data has array of arrays. I appreciate any direction or ideas anyone can suggest. Thanks