Hello!
I have a nested field in Big query table that I want to unnest and use it in an extended views with “extends” parameter.
For example:
-- model fileconnection: "bigquery_publicdata_standard_sql"explore: persons { #Repeated nested object join: persons_cities_lived { view_label: "Persons: Cities Lived:" sql: LEFT JOIN UNNEST(persons.citiesLived) as persons_cities_lived ;; relationship: one_to_many } #Non repeated nested object join: persons_phone_number { view_label: "Persons: Phone:" sql: LEFT JOIN UNNEST([${persons.phoneNumber}]) as persons_phone_number ;; relationship: one_to_one }}-- view filesview: persons { sql_table_name: bigquery-samples.nested.persons_living ;; dimension: id { primary_key: yes sql: ${TABLE}.fullName ;; } dimension: fullName {label: "Full Name"} dimension: kind {} dimension: age {type:number} dimension: citiesLived {hidden:yes} dimension: phoneNumber {hidden:yes} measure: average_age { type: average sql: ${age} ;; drill_fields: [fullName,age] } measure: count { type: count drill_fields: [fullName, cities_lived.place_count, age] }}view: persons_phone_number { dimension: areaCode {label: "Area Code"} dimension: number {}}view: persons_cities_lived { dimension: id { primary_key: yes sql: CONCAT(CAST(${persons.fullName} AS STRING),'|', CAST(${place} AS STRING)) ;; } dimension: place {} dimension: numberOfYears { label: "Number Of Years" type: number } measure: place_count { type: count drill_fields: [place, persons.count] } measure: total_years { type: sum sql: ${numberOfYears} ;; drill_fields: [persons.fullName, persons.age, place, numberOfYears] }}
I would like to override the place field from citiesLived something like that:
view: cities {
extends: persons_cities_lived
dimension: place {
label: “Place extended”
}
Can you help me please?
Thank you!