How to create an array of structs containing aggregated data in BigQuery?

Question

I have a table with event data from Firebase in Google BigQuery with the following columns:

event_date, event_timestamp, event_name, (etc..).

To count the number of events by event_name I use the following query:

SELECT event_name, count(event_name) as event_count
FROM `analytics.events_20200510`
GROUP BY event_name;

which results in:

I want to convert this result to an array of structs, formatted as JSON. Example:

{
    "events": [
        {"first_open": 69},
        {"screen_view": 510},
        {"user_engagement": 354},
        {"...": ..}
    ]
}

I started with

SELECT TO_JSON_STRING(
STRUCT(
  ??? as events
), true)

but I'm stuck and don't know what should go on the ??? to make it work. Any help would be highly appreciated.

Edit:

The answer below:

SELECT TO_JSON_STRING(
STRUCT(
  (SELECT ARRAY (SELECT AS STRUCT event_name, count(*) AS count FROM `analytics.events_20200510` GROUP BY event_name)) AS events
), true);

results in

{
  "events": [
    {
      "event_name": "session_start",
      "count": 69
    },
    {
      "event_name": "screen_view",
      "count": 510
    },
    (...)
  ]
}

which is almost correct, but I would like the event names to be the 'key' and the count to be the 'value' in the struct (as in my JSON example). Would this be possible?

Please provide sample data and desired results. I also can't tell what your problem is -- calculating the values (which are not defined) or structuring as JSON. — Gordon Linoff
– Gordon Linoff, Commented May 13, 2020 at 11:30

Mikhail Berlyant · Accepted Answer · 2020-05-14 08:01:26Z

1

Below is for BigQuery Standard SQL

#standardSQL
SELECT '{"events": [' || STRING_AGG(events) || ']}' AS json
FROM (
  SELECT FORMAT('{"%s": %i}', event_name, COUNT(event_name)) AS events
  FROM `analytics.events_20200510`
  GROUP BY event_name
)

if you would run it against same data as in your question - result will be

Row json     
1   {"events": [{"session_start": 69},{"screen_view": 510},{"user_engahement": 354}]}

answered May 14, 2020 at 8:01

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

hlagos · Accepted Answer · 2020-05-13 14:35:48Z

0

This should work

with data as (
  select 'event_type1' as event_type
  union all
  select 'event_type2' as event_type
  union all
  select 'event_type3' as event_type
  union all
  select 'event_type1' as event_type
  union all
  select 'event_type2' as event_type
  union all
  select 'event_type1' as event_type
) 
select TO_JSON_STRING(t) from (
SELECT  array (
select as struct count(*) as event_count, event_type from data group by event_type
) as events
) t;

Output

{"events":[{"event_count":3,"event_type":"event_type1"},{"event_count":2,"event_type":"event_type2"},{"event_count":1,"event_type":"event_type3"}]}

answered May 13, 2020 at 14:35

hlagos

7,9873 gold badges26 silver badges46 bronze badges

1 Comment

Koen Over a year ago

Thanks for your answer. The formatting however is different from my JSON example. Isn't it possible that in the struct the event_name acts as a key, and the event count as a value?

Collectives™ on Stack Overflow

How to create an array of structs containing aggregated data in BigQuery?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related