When the Schema Is Inside the Data

Concepts covered: JSON Extraction, Common Table Expressions, Grouping, Counting

How the question disguises itself

The request almost never contains the word JSON. It names a field instead: count events per user, average order value by platform, crashes by app version. Then the table on screen has a column called payload or properties (sometimes attributes, context, metadata, raw), and the field you need is nowhere in the column list. That gap is the tell. When a noun from the question has no matching column, read the sample rows, because the field is almost always sitting inside one of those values.

Here user_id and event_type exist only as keys inside payload. The database holds that value as one document and keeps no record of the keys within it. WHERE and GROUP BY operate on columns, as does a JOIN condition, so a key inside a document is out of their reach until a function reads it out and returns an ordinary value. Writing GROUP BY user_id against this table fails with column does not exist, and that error in the first minute is how many candidates discover the problem.

Where the first minute goes wrong

The usual stumble after that error is trying JSON functions until one works: JSON_EXTRACT, then JSON_VALUE, then an arrow operator, each with a slightly different path. The interviewer reads that as unfamiliarity with event data, even when the third attempt is right. The aggregate at the end is usually a plain COUNT. The marks come from reaching the columns that feed it in one clean pass, with a function you named before typing.

Extract first, then write ordinary SQL

The boundary is one CTE. It reads payload and turns each key the question needs into a named column; everything after it is the query you would write against a flat table. Say the plan before typing: 'user_id and event_type are keys inside payload, so I'll extract both into columns in a CTE and group on those.' That sentence costs 10 seconds and tells the interviewer you have recognised the pattern.

WITH extracted AS (
  SELECT payload->>'user_id' AS user_id,
         payload->>'event_type' AS event_type
  FROM events
)
SELECT user_id, event_type, COUNT(*) AS event_count
FROM extracted
GROUP BY user_id, event_type

The animation runs on 4 events cut down to the 2 columns and 2 keys this query reads. The CTE produces a user_id and an event_type for every payload, and from that point the outer query never sees JSON. The GROUP BY folds the 4 rows into 3 groups: u_101 with page_view counted twice (events 1 and 4), u_101 with purchase once, and u_102 with page_view once. Events 1 and 4 share a group because, once extracted, their values are equal strings like any other column's.

Why the extraction lives in a CTE

Extraction repeated inline

• payload->>'user_id' written out in SELECT and GROUP BY, then again in every filter
• Dialect syntax tangled into the business logic
• A renamed key means finding and fixing every copy
• Testable only by running the full aggregate

Extraction once, in a CTE

• Each key read out once and given a column name
• The outer query reads like SQL over a flat table
• A renamed key or a new engine touches one CTE
• SELECT * FROM extracted LIMIT 5 shows the parsed rows first

The last pair is the one that saves a live round. Run the CTE by itself on 5 rows before writing the aggregate. If user_id comes back NULL on every row, the path is wrong, and you learn it in seconds instead of while explaining an empty result to the interviewer.

-- Events per user and type for 2024-03-14 (Postgres, payload is JSONB)
WITH extracted AS (
  SELECT
    payload->>'user_id'    AS user_id,
    payload->>'event_type' AS event_type
  FROM events
  WHERE received_at >= DATE '2024-03-14'
    AND received_at <  DATE '2024-03-15'
)
SELECT user_id, event_type, COUNT(*) AS event_count
FROM extracted
GROUP BY user_id, event_type
ORDER BY event_count DESC;

Questions worth asking first

About this lesson

This section is part of the Semi-Structured Data: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.