Aggregate a Page View Log — JSONB Aggregation in SQL

The problem

Streamhub's page analytics dashboard needs a complete structured log of every page-view event as a single JSON array.

Write a query to return one JSON array collecting every event whose event_type is 'page_view'. Each array element is a JSON object with keys 'id' (the event ID) and 'page' (the page path from the event's properties).

Assumptions:

Page-view events have event_type = 'page_view' and a 'page' key in properties carrying the page path.
Only page-view events contribute to the array. The result is a single output row containing the entire array.
The array contains one element per qualifying event (no de-duplication).

Output:

A single row with one column, page_view_log, containing the JSON array.

Schema · analytics 5 tables

users

id integer

name text

email text

country text

plan text

signed_up_at timestamptz

is_active boolean

conversions

id integer

user_id integer

converted_at timestamptz

plan text

amount numeric

sessions

id integer

user_id integer

started_at timestamptz

ended_at? timestamptz

event_count integer

events

id integer

user_id integer

session_id? integer

event_type text

occurred_at timestamptz

properties? jsonb

periods

id integer

name text

start_month integer

end_month integer

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  JSONB_AGG(JSON_BUILD_OBJECT('id', id, 'page', properties ->> 'page')) AS page_view_log
FROM
  events
WHERE
  event_type = 'page_view'

The shape

With no GROUP BY, jsonb_agg runs as a single aggregate over the entire filtered row set and produces one row of output containing one JSONB array. The whole page-view log lands in a single cell.

Clause by clause

SELECT jsonb_agg(json_build_object('id', id, 'page', properties->>'page')) AS page_view_log builds the inner object for each surviving event row — 'id' from the event's id column, 'page' from the JSONB properties->>'page' extraction — and then jsonb_agg collects every one of those objects into a single array. With no grouping column listed, the aggregate covers the entire row set.
FROM events reads the event records.
WHERE event_type = 'page_view' keeps only the page-view rows. Every surviving row contributes one object to the array; non-page-view events never enter the aggregation.

Why this and not a `GROUP BY`

A GROUP BY would produce one row per group, which is the wrong shape here. The prompt asks for a single row holding the full log — a single aggregation over the whole filtered set, not a per-something split. Omitting GROUP BY from a query that has an aggregate but no other selected columns is the canonical "one summary row" form.

You practiced jsonb_agg(json_build_object(...)) without a GROUP BY — when no grouping column is specified, the entire restricted record set collapses into a single one-row array.

Return one JSON array collecting every event whose `event_type` is `'page_view'`. Each array element is a JSON object with keys `'id'` (the event ID) and `'page'` (the page path from the event's `properties`)

The shape

Clause by clause

Why this and not a `GROUP BY`

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.

The shape

Clause by clause

Why this and not a GROUP BY

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.

Why this and not a `GROUP BY`