Nesting query results in BigQuery

user_5908830 0
Viewed 97

I have a query that produces a result that looks like the following:

session_id country vendor_id page_name qty_visits
123 a abc home 1
123 a abc shop 4
456 b abc home 78
456 b xyz home 9
456 b xyz cart 6

I want to nest this table so that page_name and qty_vistis are arrays inside a struct called page_visits. Basically, my end result should be a table with three rows (identified by session_id, country, and vendor_id), with a fourth column called page_visits which itself is a struct with two columns, each of which is an array. One of said columns has a list of all the pages visited, the other one has the number of times it was visited. Like so

session_id country vendor_id page_visits.page_name page_visits.qty_visits
123 a abc home 1
shop 4
456 b abc home 78
456 b xyz home 9
cart 6

As a check, the result of select count(*) from final_table should be 3, as opposed to 5 (which is what one would get without the nesting).

google-bigquery
1 Answers

Use array_agg:

select
  session_id,
  country,
  vendor_id,
  array_agg(struct(page_name, qty_visits)) as page_visits
from mytable
group by session_id, country, vendor_id
user_134735250
Related