CODEWITHSUNDEEP
sqlgoogle-bigquery
ytpm
ytpm

Reputation: 5150

Get SUM of value of key inside an array of objects

I am trying to get data from analytics table and sum it up, but I cannot figure out on how to get it.

This is the data structure:

[{
    "event_name": "paid_ad_impression",
    "event_params": [{
        "key": "valuemicros",
        "value": {
          "string_value": "26072",
          "int_value": null,
          "float_value": null,
          "double_value": null
        }
    }]
}]

So I am trying to get the SUM of all paid_ad_impression valuemicros (which is a string).

Upvotes: 0

Views: 190

Answers (1)

Max Zolotenko
Max Zolotenko

Reputation: 1132

select  sum(safe_cast(t2.value.string_value as numeric))  as totalSUM_from_string,
        sum(t2.value.int_value)                           as totalSUM_from_int,
        sum(t2.value.float_value)                         as totalSUM_from_float,
        sum(t2.value.double_value)                        as totalSUM_from_double,
from    `YOUR_PROJECT.YOUR_ANALYTICS_DATASET.events_*` t -- !!! here set your project, dataset and table name with analytics
inner join unnest(t.event_params) t2 on t2.key = 'valuemicros'
where   t.event_name = 'paid_ad_impression'

Upvotes: 1

Related Questions