Question

Array column

  • 14 May 2022
  • 2 replies
  • 125 views

Hi there,

Would like some input on how to create a dimension from an array column in Lookml. 

 

Warm regards.


2 replies

Userlevel 7
Badge +1

Which dialect do you use? I’ve used array with Snowflake and BigQuery. As long as unnesting an array doesn’t impact performance too much, I use principles outline on this page:

https://help.looker.com/hc/en-us/articles/360023638874-Nested-Data-in-BigQuery-Repeated-Records-

Which dialect do you use? I’ve used array with Snowflake and BigQuery. As long as unnesting an array doesn’t impact performance too much, I use principles outline on this page:

https://help.looker.com/hc/en-us/articles/360023638874-Nested-Data-in-BigQuery-Repeated-Records-

 I am using Bigquery, I had referred the article. However  I feel as though my problem is in defining the View to join with the master1. Could you please have a look

connection: "bigquery"

## trying to define an array column based on the reference link but having errors. Below is a snippet(adjusted) of my SQL codes.

## working with two views "Master1" and "Array_column" with the second being the array column. Also, the array in the database is termed "Ownersname1" with the names repeating.

explore: Master 1 {
# Repeated nested object
join: Array_column {
view_label: "Array Names"
sql: LEFT JOIN UNNEST(Master1.Ownnername1) as Owners_name ;;
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: Master1 {
sql_table_name: `bigquery-samples.nested.persons_living` ;;

dimension: id {
primary_key: yes
sql: ${TABLE}.fullName ;;
}
dimension: gender {}
dimension: fullName {label: "Full Name"}
dimension: age {type:number}

measure: count {
type: count
drill_fields: [fullName, age]
}

dimension: Ownersname1 {hidden:yes}




view: Array_Column {
dimension: id {
primary_key: yes
sql: CONCAT(CAST(${Master1.fullName} AS STRING),'|', CAST(${place} AS STRING)) ;;
}
dimension: place {}
dimension: numberOfYears {
label: "Number Of Years"
type: number
}
}




 

Reply