6 chapters · 18 lessons
First lesson: A document in a cell: object, array, value
A hands-on course on JSON in SQL · PostgreSQL, ClickHouse, BigQuery
JSON inside a SQL table.
From nested fields to metrics.
Most SQL courses stop where the table stops being rectangular — this one starts there. You learn to pull values out of nested data, unnest it without losing rows and compute metrics on top of JSON, hands-on in PostgreSQL, ClickHouse and BigQuery.
The first chapter is free · the other 5 chapters are part of Pro · What Pro includes
- After every unnest, a check: what one row means now
- Exercises are graded by the query result
- Live SQL cells in every lesson
- Zero installation: all in the browser
CROSS JOIN LATERALjsonb_array_elements(payload->'items') iCROSS JOIN LATERALjsonb_array_elements(payload->'coupons') c
| order | item | coupon |
|---|---|---|
| 5003 | SQL для профи | KOTO10 |
| 5003 | SQL для профи | BOOKS5 |
| 5003 | Чистый код | KOTO10 |
| 5003 | Чистый код | BOOKS5 |
| 5003 | Алгоритмы на практике | KOTO10 |
| 5003 | Алгоритмы на практике | BOOKS5 |
What hurts
A task from work — and the technique that closes it
App events, payment gateway responses, product attributes and user settings — all in a single jsonb column. Under each task, the technique from the program and what the chapter covers.
After unnesting an array, some orders are gone
LEFT JOIN LATERALAn empty array and a missing key yield zero rows, and the order vanishes without a single error. The arrays chapter teaches you to unnest without losing the order and to check the type first.
An order total doubled after a join
row explosionTwo arrays in one document multiply rows: three items and two coupons make six. Each array is aggregated on its own, before the join.
IS NULL finds the wrong rows
three kinds of nothingA missing key; a JSON null in the key; no document at all, so the cell is SQL NULL. Three different states with three different business meanings. The course teaches you to tell them apart instead of merging them with one COALESCE.
The price arrives as a number, a string or an object
types5990, "1290", "" and {"amount": 3490} in one field. Find out the type with jsonb_typeof first, then cast — and do not fall over on the first empty string.
After a release the metric dropped, yet the store works as before
schema driftThe event author moved from user.id to actor.id. The old query does not fail — it returns NULL. The dirty-data chapter builds a quality report that notices this before the dashboard does.
A PostgreSQL query does not run in ClickHouse or BigQuery
three dialectsOne task — item totals by category without losing orders — is solved again in each engine: JSONExtract and arrayJoin, JSON_VALUE and UNNEST, JSON versus ARRAY<STRUCT>.
This course is for you if
- Analysts whose product events live in a payload column rather than in tidy tables
- Data engineers and backend developers who keep API responses, settings and attributes in jsonb
- Anyone who writes SQL with confidence but gets lost when a cell holds an array
- Anyone working with ClickHouse or BigQuery who wants to see how their JSON differs from PostgreSQL
And NOT for you if
- Anyone who has never written SELECT and JOIN: SQL basics are not taught here
- Anyone who needs JSON in JavaScript or Python: the course is about SQL and data in the database
- Anyone looking for a MongoDB course: here JSON lives inside a relational database
How it works
Document — unnest — check
Every lesson has live cells, a trap quiz and a fix-the-query exercise. Queries run on real data at once, and the traps are built so that the SQL is valid while the answer is not.
What one row means now
After every unnest and join you compare COUNT(*) with COUNT(DISTINCT …) per order or event. Nearly every trap in the course is caught by this check.
Messy data, not a perfect payload
App events in three schema versions, payment gateway responses in two API versions, product cards, customer settings and a raw dump with some non-JSON in it. Nearly every row of the dataset exists for a specific mistake.
Graded by the result
In a fix-the-query exercise your SQL runs in the sandbox and is compared with the reference. Any query that gives the same answer passes. Every lesson ends with two exercises in the trainer: the lesson counts as done once at least one is solved.
The ninth shift of one story
Boxes inside boxes
Along with the Kotomarket database and the raw text, the signal brought Vault-9 a stack of parcels — the store machines’ service traffic from the two weeks before the cut-off, 10 to 24 February 2025. The app reported every tap, the payment gateway answered the store, product cards kept attributes that never fit into tables. Everything is packed in curly braces: one column in the table, whole trees inside.
The parcels lie in the Unpacking Bay, next door to the Sorting Room. The Commandant wants a summary of the store’s last two weeks: what people bought, how many orders there were, how they paid. The mentor is the same — QUERY, the old archive intelligence with the temper of a cat: “A table is a cupboard with shelves. JSON is a box full of boxes. First we learn to open them, then not to lose what is inside.”
The program
6 chapters, 18 lessons, 9 h 59 min
Lessons and progress for every chapter; the first chapter is free, the rest are part of Pro.
JSON in a cell
Kotomarket’s table has one column with a whole tree inside it. The chapter teaches you to open a document: object, array and scalar, json and jsonb, the -> and ->> operators. And to tell three kinds of nothing apart: a missing key, JSON null and SQL NULL.
Paths and search inside a document
On 17 February the payment gateway changed its response format, and a path that worked yesterday returns NULL today. The chapter covers nested paths with #> and #>>, searching for a fragment with @> and ? — and exploring an unfamiliar document before writing the query.
Arrays become rows
The core chapter of the course. jsonb_array_elements and LATERAL turn a cart into rows — and break the report right away: an empty array silently swallows an order, a null or a string instead of an array crashes the query, and two arrays in one document multiply the rows.
From JSON to tables and back
The station’s inventory accepts only tables. jsonb_to_record and jsonb_to_recordset lay a document out into fields, jsonb_typeof and pg_input_is_valid check the type before casting, and jsonb_build_object and jsonb_agg pack the summary back into JSON.
Dirty data: the query is right, the metric lies
After 17 February the summary showed half as many buyers as before, and conversion falling from 75% to 37.5%. The chapter proves that the event schema broke, not the store: three versions of the event author, different meanings of empty, and a quality report that spots drift before the dashboard does.
Three worlds: PostgreSQL, ClickHouse, BigQuery
Copies of the parcels went to the Reading Room and the Far Archive. The task from the arrays chapter — item totals by category without losing orders — is solved again in ClickHouse and BigQuery. And the last lesson answers what to keep in JSON and what to move into columns and indexes.
The honest boundary
What is not in the course, and why
Document databases: MongoDB and its kin
They have their own query language and data model. The course is about JSON inside a relational database, where an analyst meets it most often.
Instead, the last lesson teaches you to decide what belongs in a document and what goes into columns — a skill that carries over to document stores.
JSON_TABLE and the new PostgreSQL 17 functions
The course runs on PostgreSQL 16: code that will not run in the sandbox is not worth teaching.
The new functions appear in “in newer versions” notes, while PostgreSQL 16 tools do the same job: jsonb_to_recordset is covered in chapter 4, and jsonpath in a practical case in chapter 5.
ClickHouse’s new JSON type
In version 24.10, which the course uses, it is experimental and its behaviour is still changing.
The chapter’s task is solved with JSONExtract and arrayJoin, which work today, and the new type is described in a side note.
FAQ
Frequently asked
Yes: SELECT, WHERE, GROUP BY and JOIN. Everything about JSON — operators, functions, unnesting arrays and LATERAL — is explained from scratch.
The parcels are waiting to be unpacked
The first chapter is open in full — 3 lessons, no subscription and no card.