First Unnest then Join

[SergeyB]

Dear Looker Community,

I stuck with a problem and need your help.

I have an explore (“client_info”), which keeps all information about clients.

As main, i take “client” view and add all additional information to it from other views. I want to bring info from view “Abo”, which in fact has repeated rows.

“Client” view has PK “id_client” + FK “abo_option_id”

“Abo” view has PK “id_abo” + repeated rows with key “id_abo_option” + some attributes, such as “abo_option_price”

client	abo
id_client abo_option_id client_1 abo_opt_A client_2 abo_opt_D	id_abo option.id_abo_option option.id_abo_option abo_1 abo_opt_A 10   abo_opt_B 20 abo_2 abo_opt_C 10   abo_opt_D 15

As a result:

id_client	abo_option_id	id_abo_option
client_1	abo_opt_A	10
client_2	abo_opt_D	15

Indeed i checked the documentation, but what i found is a case, when the join is based on id_abo + unnest on repeated structure (abo__option)

https://cloud.google.com/looker/docs/best-practices/how-to-model-nested-bigquery-data-in-looker

What could be a solution here?

Best regards,

Sergey

[SergeyB]

Edit:

The solution for a similar, but easier problem, if the link between client and abo views would be over "id_abo", is the following:

explore: client_info {
   from: client
   join: abo {
      relationship: one_to_many
      sql_on: ${client.id_abo} = ${abo.id_abo} ;;
   }

   join: abo__option {
      relationship: one_to_many
      sql: LEFT JOIN UNNEST(${abo.option}) as abo__option ;;
   }
}

[SergeyB]

I resolved the problem. Here is the solution, happy if it can be improved or please point to the problems.

explore: client_info {
   from: client
   
  join: abo__option {
    relationship: many_to_one
    sql: LEFT JOIN (SELECT abo__option.* FROM `PATH_TO_DATASET` AS a, UNNEST(a.Option) as abo__option ) as abo__option ON client.abo_option_id = abo__option.id_abo_option;;
  }
 }