Question

Extend an unnested field

  • 8 December 2022
  • 0 replies
  • 97 views

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!


0 replies

Be the first to reply!

Reply