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.