Trouble with Modeling nested Data 2 levels deep - Scalar subquery produced more than one element

[marc]

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

[Dawid]

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

[marc]

Hi Dawid, your assumption is correct. The data has array of arrays. I appreciate any direction or ideas anyone can suggest. Thanks