Creating Primary Key for repeated nested field in BQ (Community)

View the original community article here

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}) ;;

}