A question about how to select the value of a event_params.key in big query

Question

Our project has some events recording how long the time that a user stay in a page. We add a event_params.key named time_ms, and its value shows the duration. How can I select the sum of 'time_ms'?

I tried to use SQL statements but failed.

SELECT *
FROM analytics_152426080.events_20190626
WHERE event_name = 'details_viewtime' AND
      event_params.key = 'time_ms'

It shows the error message:

'Cannot access field key on a value with type ARRAY<STRUCT<key STRING, value STRUCT<string_value STRING, int_value INT64, float_value FLOAT64, ...>>> at [7:20]'.

I expect to get the sum of 'time_ms', but I should solve this question first.

Answer 1

I think you need unnest:

SELECT *
FROM analytics_152426080.events_20190626 e CROSS JOIN
     UNNEST(event_params) ep
WHERE e.event_name = 'details_viewtime' AND
      ep.key = 'time_ms';

I'm not sure where the actual value is located, but something like this:

SELECT SUM(ep.value.int_value)
FROM analytics_152426080.events_20190626 e CROSS JOIN
     UNNEST(event_params) ep
WHERE ep.event_name = 'details_viewtime' AND
      ep.key = 'time_ms';

Assuming the value you want to sum is an integer.

This assumes that the value column is a number of some sort. Otherwise, you need to convert it to one.

Or, if you want to sum the value per row:

SELECT e.*,
       (SELECT SUM(ep.value.int_value)
        FROM UNNEST(event_params) ep
        WHERE ep.key = 'time_ms'
       ) as sum_ms
FROM analytics_152426080.events_20190626 e
WHERE e.event_name = 'details_viewtime'