Dear Stackoverflow 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 view
| id_client | abo_option_id |
|---|---|
| client_1 | abo_opt_A |
| client_2 | abo_opt_D |
Abo view
| id_abo | option.id_abo_option | option.price |
|---|---|---|
| abo_1 | abo_opt_A | 10 |
| abo_opt_B | 20 | |
| abo_2 | abo_opt_C | 10 |
| abo_opt_D | 15 |
As result:
| id_client | abo_option_id | price |
|---|---|---|
| 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
The solution for a similar, but easier problem, if the link would be over "id_abo", would be 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 ;; } }
What could be a solution here?
Best regards, Sergey
