Creating couple columns from a single nested field

Hi,

We store event log properties in nested fields, as shown below:

 

 

dimension: message_number {
    type: number
    view_label: "Event Properties"
    sql: (
                                select
                                 logs__properties__value.int_value from
                                  UNNEST(${TABLE}.properties) logs__properties
                                  LEFT JOIN UNNEST([logs__properties.value]) logs__properties__value where  lower(logs__properties.key) = 'message_number') ;;
  }
dimension: character_count {
    type: number
    view_label: "Event Properties"
    sql: (
                        select
                         logs__properties__value.int_value from
                          UNNEST(${TABLE}.properties) logs__properties
                          LEFT JOIN UNNEST([logs__properties.value]) logs__properties__value where  lower(logs__properties.key) = 'character_count') ;;
  }

 

 

The problem is that when we create measures using these dimensions, they generate multiple subqueries, which are inefficient in BigQuery.

Since event properties are dynamic, is there a way to UNNEST only once while still allowing access to multiple properties efficiently?

Thank you,

Berat S.

0 1 195
1 REPLY 1
Top Labels in this Space