Knowledge Drop
Last tested: Apr 3, 2020
If you run into a BQ challenge where no primary key was present in any of the nested fields, records could be identical, so a PK couldn’t be created through field concatenation
A solution is to define the PKs in the join as below.
join: view__repeated_field { view_label: "View: Repeated Field" sql: LEFT JOIN UNNEST(ARRAY( (SELECT AS STRUCT *,GENERATE_UUID() as id FROM UNNEST(${view.repeated_field})) )) as view__repeated_field;; relationship: one_to_many }
Once created, you then add the id dimension to your nested view and concatenate it with the parent view’s primary key.
dimension: id {
hidden: yes
type: string
sql: ${TABLE}.id ;; }
dimension: PK {
hidden: yes
primary_key: yes
type: string
sql: concat(${id}, ' ' ,${parent_view.primary_key_field}) ;;
}