Knowledge Drop

Creating Primary Key for repeated nested field in BQ

Userlevel 5

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



This content is subject to limited support.                



0 replies

Be the first to reply!