Extract Page from Event Properties — JSONB Field Extraction in SQL

The problem

Streamhub's product analytics team tracks which pages users are visiting across the platform.

Write a query to return the event ID, user ID, and page path for every event whose event_type is 'page_view'. The page path is stored under the 'page' key in the event's properties JSONB column.

Assumptions:

The events table has one row per event with an id, a user_id, an event_type, and a properties JSONB column.
A page-view event has event_type = 'page_view' and a 'page' key in its properties carrying the page path as a text value.
Only 'page_view' events should appear. The page-path column carries the text-typed extraction of the 'page' key.

Output:

One row per qualifying event, with columns id, user_id, and page_viewed.

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
  id,
  user_id,
  properties ->> 'page' AS page_viewed
FROM
  events
WHERE
  event_type = 'page_view'

The shape

JSONB extraction is column-agnostic. The ->> operator works the same way against the analytics events.properties column as it does against products.attributes — the column's name and table don't change how the operator behaves, only the JSONB document it navigates. The page-view filter on event_type is a regular column comparison; the page path is the JSONB extraction.

Clause by clause

SELECT id, user_id, properties ->> 'page' AS page_viewed returns the event's ID and user ID from their columns, then reaches into the properties JSONB document for the value at the 'page' key and returns it as text. The AS page_viewed alias makes the column read as a domain quantity in the result.
FROM events reads the event log, where every row has its own properties JSONB document carrying whatever payload fields the event type recorded.
WHERE event_type = 'page_view' filters to page-view events only. This is a plain column comparison — event_type is a regular text column, not a JSONB field. Filtering on it first means the JSONB extraction in the SELECT only runs against the rows that actually have a 'page' key on record.

Why this and not filter on the JSONB field

The same result could be reached by filtering on properties ->> 'event_type' = 'page_view' if the event type lived inside the JSONB document. But here event_type is a top-level column, so filtering on it directly is faster (no per-row JSONB navigation) and clearer. The JSONB extraction in the SELECT happens after the filter, so it only runs against the rows that pass the event_type check. Reaching into JSONB makes sense for fields that vary by event type; reaching past the top-level column to get to a JSONB version of the same field would be the wrong shape.

You practiced JSONB extraction across schemas — the same ->> operator over a different column name (properties instead of attributes); the semantics of the operator are independent of the column it operates on.

Return the event ID, user ID, and page path for every event whose `event_type` is `'page_view'`. The page path is stored under the `'page'` key in the event's `properties` JSONB column

The shape

Clause by clause

Why this and not filter on the JSONB field

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