The whole course has assumed you know which columns you need. And that's nearly always true: an order has a customer, a date, a status and shipping costs, and that doesn't change. But some data has no fixed shape. An olive oil has acidity, olive variety and extraction method; a cream has ingredients, skin type and volume; a bamboo toothbrush has none of those things and does have a bristle type. Adding a column per attribute would give you an eighty-column table that's almost entirely null, and building a key-value pair table —the classic EAV— turns any query into a jigsaw of self-joins.

That's what PostgreSQL has JSONB for: a column storing a document with its own structure, indexable and queryable with SQL. This is where 08-03's promise (GIN indexes over JSON) gets kept, and it covers the case of an API response, user preferences or an event log. You'll see how to build documents, how to read them, how to modify them, how to turn them back into rows so you can keep using all the SQL in this course, and —the most important part of the lesson— what should never go inside a JSON.

1. When a fixed schema isn't enough
2. JSON versus JSONB
3. Building documents
4. GreenStore's products.attributes column
5. Accessing: ->, ->>, #>, #>>
6. Searching: containment, existence and JSONPath
7. Modifying documents
8. Expanding to rows: the bridge back to SQL
9. Indexing with GIN
10. The design discussion: what does NOT go in JSON
11. Common Mistakes and Tips
12. Exercises
13. Module conclusion

Four situations in which the pure relational model gets uncomfortable:

And the three classic ways out, with their price:

PostgreSQL has two types, and choosing the wrong one costs you.

The practical rule: use JSONB unless you need to preserve the exact text. And that "unless" is very narrow: basically, storing an external service's literal response because you have to verify a digital signature or reproduce it byte for byte. For everything else, JSONB. The difference shows up immediately:

The json stores the nonsense exactly as it is —repeated a key included—; the jsonb normalizes, sorts and keeps the last value of a.

The star case —returning a whole order with its lines nested in a single row, which is what an API needs— combines the last three:

One row, one column, order 1 complete with its €42.10 across three lines. Notice two things: jsonb_agg accepts its own ORDER BY inside the parentheses, and the keys come out in a different order from the one you wrote them in, because it's jsonb. This saves the application the work of reassembling flat rows into a nested object, and it's why many modern APIs return straight through whatever the database produces — the link to 11-05.

And for a compact summary, jsonb_object_agg:

The five categories with sales and their canonical figures, in a single value.

From here on we work with a new column. It isn't canonical: it's this lesson's example and no other module uses it.

Six products with attributes; the other fourteen have attributes at NULL, which is normal for this kind of column and something the queries will have to take into account.

Four operators, and the difference between them is the number-one source of errors with JSON in PostgreSQL:

The mnemonic: the double arrow >> pulls the value out "raw", as text. The single one still returns JSON, and that lets you chain: attributes -> 'certifications' ->> 0 steps down into the array with -> and pulls the first element out as text with ->>.

Three things to spot in that table. First: "Spain" with quotes isn't Spain; comparing attributes -> 'origin' = 'Spain' fails because there's a jsonb on the left and a text on the right — you have to use ->>, or compare against '"Spain"'::jsonb. Second: everything coming out of ->> is text and has to be cast (::int, ::numeric) to compare as a number; otherwise '200' < '50' is true because it compares alphabetically. Third: a key that doesn't exist returns NULL, not an error — convenient, and dangerous, because a mistyped key doesn't complain.

The previous operators extract; these filter, and they're the ones that make use of the GIN index.

Four vegan products. Notice how powerful @> is: it searches inside an array without taking it apart and without knowing which position the element is in. And SELECT id FROM products WHERE attributes ? 'volume_ml' returns 2 rows —products 6 and 8, the only ones with that key— which is how you ask "which products have this attribute defined".

For conditions @> can't express —numeric comparisons, filters inside arrays, expressions— PostgreSQL 12 added JSONPath, an XPath-style path language:

The first returns one row, product 8 (body oil, 200 ml): number 6 has 50 and the rest don't have the key. The second returns five: cosmos-organic, eu-organic, gluten-free, vegan and —as soon as you add any new product— whatever it brings with it. Read $.volume_ml ? (@ > 100) like this: $ is the root of the document, .volume_ml steps down to that key, ? (...) is a filter and @ is the current value. With [*] you walk every element of an array.

And here's the performance detail you have to internalize: an UPDATE on a JSONB column rewrites the entire document. There's no such thing as "updating a key"; PostgreSQL creates a new version of the whole row with the whole document (it's MVCC, 09-02). Changing a boolean in a 200 KB document writes 200 KB. The practical consequence: JSONB is for writing rarely and reading a lot. If a field is updated constantly, that field wants to be a column.

A fine point: jsonb_set with a SQL NULL in any argument returns NULL, not the untouched document — and since it's an UPDATE, it wipes the entire document without warning. Use COALESCE, or jsonb_set(COALESCE(attributes, '{}'::jsonb), ...), when the column can be null.

This section is the one that connects JSON to everything else in the course: turning a document into rows so you can group, join, sort and apply window functions to it.

And there's nothing JSON about this any more: it's a module 4 GROUP BY over ordinary rows, with 07-04's LATERAL acting as the bridge. That's exactly the point: once expanded, a document is just another table, and everything you've learned across eleven lessons applies again. jsonb_to_recordset goes one step further and produces typed columns directly:

It's the canonical way to receive a list of order lines from an API and turn it into insertable rows with a single INSERT ... SELECT — far cleaner than the two parallel arrays of sp_confirm_order in 10-04.

This is where 08-03's promise gets kept. Without an index, every query with @> walks the whole table and parses every document. With a GIN index, it doesn't.

The criterion: if you only query by containment (@>), jsonb_path_ops, which is smaller and faster. If you need to ask about key existence (?), there's no way around jsonb_ops. And remember from 08-03 that a GIN is built and maintained slowly: it's the "write rarely, read a lot" index, which happens to be the profile of a JSONB column too.

And the third way, which is often the best: a B-tree over an expression when the query always goes through the same key.

It returns products 8 and 15. A B-tree over (attributes ->> 'origin') is tiny compared with a GIN, supports ranges and ordering, and it's the right choice when one specific key is queried a lot. The double parentheses are compulsory: it's an expression index, 08-02's kind — and it works because ->> is IMMUTABLE, the condition 10-04 explained.

The most important part of the lesson. JSONB is so convenient that it tempts you to put everything inside it, and that's a decision you pay for over years.

What must NOT go in a JSON:

• Anything you filter or join on constantly. category_id inside a JSON turns an index JOIN into a query you have to rewrite with ->> and cast on every use.
• Anything with referential integrity. A foreign key can't point inside a document. If you store {"supplier_id": 7} and somebody deletes supplier 7, nobody warns you: you've just lost the guarantee 01-05 gave you for free.
• Anything that's really a table. An array of a thousand orders inside a customer's document is an orders table in disguise, with no indexes of its own, unable to be queried on its own and rewritten in full with every purchase.
• Anything with rules. There's no NOT NULL, no UNIQUE and no CHECK inside a JSON: a price in JSON can end up being "expensive" and nothing will stop it. And anything updated constantly, because every UPDATE rewrites the whole document (section 7).

The antipattern: using the relational database as a document store out of laziness about modelling. You recognize it by a table with an id and a data JSONB column holding everything. It works for the first month, and from the sixth on every query is a castle of ->> and ::numeric, nothing has an index, nothing has integrity and nobody knows which keys exist. If you know what the fields are, they're columns. If you genuinely need a document store, there are databases that do that far better.

And the table that settles the specific question:

Applied to GreenStore: price, stock and category_id are columns, no argument; the order lines are a table, not an array inside orders; and the oil's acidity or a cream's skin type are JSON, because each category has its own and they're only shown on the product page.

Dialect note: MySQL 8 has a binary JSON type with -> and ->> (with PostgreSQL's semantics), JSON_EXTRACT, JSON_TABLE to expand to rows, and no indexes over JSON: you index generated columns instead. SQLite ships the JSON1 extension compiled in by default: json_extract(), json_each(), the ->> operator from 3.38 on, and everything stored as text. SQL Server stores JSON in NVARCHAR and queries it with JSON_VALUE, JSON_QUERY and OPENJSON to expand to rows, with indexes over computed columns. Oracle has had a native JSON type since 21c and supports JSONPath extensively. The SQL:2016 standard defines JSONPath, which is why jsonb_path_query looks so similar across engines; everything else is pure dialect.

• Confusing -> with ->>. The first returns jsonb ("Spain", with quotes) and the second text (Spain). attributes -> 'origin' = 'Spain' never matches.
• Comparing numbers without casting. attributes ->> 'volume_ml' > '100' compares text: '50' is greater than '100'. Always cast: (attributes ->> 'volume_ml')::int.
• Mistyping a key. It doesn't error: it returns NULL and the row disappears silently. Check with ? which keys really exist. And using JSON instead of JSONB: no containment operators, no GIN indexes and reparsing the text on every access. JSONB unless you need the exact literal text.
• Forgetting that an UPDATE rewrites the whole document. With large documents and frequent updates, that means bloat and VACUUM work (09-02).
• jsonb_set on a NULL column. It returns NULL and wipes the document. COALESCE(attributes, '{}'::jsonb).
• Indexing with GIN by default and not measuring. If you only use @>, jsonb_path_ops takes up considerably less; and if you always query the same key, a B-tree over the expression beats both.
• Putting something with a foreign key into JSON. There's no referential integrity inside a document, and there never will be. Tip: document the expected keys. A JSON with no documented schema is a free-text field. If the set of keys is closed, validate it with a CHECK (attributes ?& ARRAY['origin']) or with a schema-validation extension.
• Tip: start with columns and move to JSON only what's left over. The other way round doesn't work: pulling three years of data out of a JSON into typed columns is an expensive migration.
• Tip: jsonb_pretty(attributes) to read a document in psql. And \x for expanded mode.

With the attributes column loaded as in section 4: (1) List the Spanish-origin products with their acidity, if they have one. (2) Count how many products there are per origin, sorted from most to fewest. (3) Find all the ones with aloe vera among their ingredients, using @>. (4) Add the key "reviewed": false to every product that has attributes, without overwriting anything already there.

Marketing wants the catalogue in JSON for the website: an array with one object per category, and inside each one, the category's name and the array of its active products with id, name and price. Write it with jsonb_agg and jsonb_build_object, and say how many elements the outer array has.

Using section 10's table, decide where each piece of data goes and justify it in one sentence:

1. A product's average rating, shown in the listing and used for sorting.
2. The packaging dimensions (height, width, depth), known for only some products and used only when computing shipping.
3. A product's price change history.
4. The full response from the payment gateway when charging an order.
5. A product's supplier.

Solution 1

1 returns two rows: the oil (id 1) with acidity 0.25 —the one section 7's jsonb_set left behind— and the rice (id 2) with NULL, because it doesn't have that key. 2 returns Portugal 2, Spain 2, France 1 and Germany 1: four origins for the six products with attributes. 3 returns one row, the face cream (id 6), and it works because @> searches inside the array regardless of position. 4 affects 6 rows, and the order of the || is the key: '{"reviewed": false}' || attributes makes attributes win if the key already existed, whereas attributes || '{"reviewed": false}' would overwrite it. It's the difference between "add if missing" and "force the value".

Solution 2

The outer array has 6 elements, one per category, including Supplements — which shows up with "products": [] because its only product, the Spirulina capsules, is discontinued (active = FALSE). That COALESCE(..., '[]'::jsonb) isn't decoration: without it, jsonb_agg over zero rows returns NULL and the key would come out as null instead of an empty array, which would break any code walking the list. It's exactly 04-04's problem with SUM over the empty set, now in JSON.

Solution 3

All five answers come out of asking three questions: is it filtered or sorted on? (column), does it have a life of its own? (table), is it optional, variable and only read together with the rest? (JSON).

You close the module with the last piece of the toolbox:

• JSONB versus JSON: decomposed binary versus literal text. JSONB normalizes keys, removes duplicates, accesses fast and is the only one that can be indexed and has containment operators. The rule: JSONB unless you need the exact text.
• Building: to_jsonb, jsonb_build_object, jsonb_build_array, and above all the aggregates jsonb_agg and jsonb_object_agg, which return order 1 complete with its three lines and its €42.10 in a single row — the star case for an API (11-05).
• Accessing: -> and #> return jsonb; ->> and #>> return text. That difference is the number-one source of errors, along with comparing numbers without casting and with non-existent keys returning NULL silently.
• Searching: @> (containment, which reaches inside arrays), ?, ?| and ?& (key existence), and JSONPath with @?, @@ and jsonb_path_query for what containment can't express, such as $.volume_ml ? (@ > 100).
• Modifying: || merges, - deletes, jsonb_set sets and jsonb_insert adds — remembering that an UPDATE rewrites the whole document, and that's why JSONB is for writing rarely and reading a lot.
• Expanding to rows with jsonb_array_elements, jsonb_each and jsonb_to_recordset is the bridge back: once expanded, a document is just another table and all the SQL in the course works again — like the certification count, with vegan on 4 products and eu-organic on 3.
• Indexing, closing 08-03: GIN with jsonb_ops supports every operator and takes up more space; jsonb_path_ops only @>, @? and @@, but it's smaller and faster; and a B-tree over (attributes ->> 'key') beats both when you always query the same key.
• And the design discussion: keep out of the JSON everything you filter or join on, everything with referential integrity, everything that's really a table, everything that needs rules and everything updated constantly. The antipattern —using a relational database as a document store out of laziness about modelling— is paid back with interest. If you know what the fields are, they're columns.

And with that module 10 closes. In six lessons you've assembled the complete toolbox: views that give a query a name and encapsulate a metric, with materialized ones for expensive reports; CTEs that turn three levels of subqueries into readable steps, and WITH RECURSIVE to walk the whole org chart and the three-hop referral chain; window functions that aggregate without collapsing and solve rankings, running totals, moving averages and top N per group; procedures where order confirmation finally lives with its guaranteed atomicity; triggers that apply on their own the rules a CHECK can't reach; and JSON for what doesn't fit in a fixed schema. You're no longer learning SQL: you're using it.

What's missing isn't more functions, but context. A real system isn't a well-written query: it's a set of decisions about how the work is organized, who can see what, what gets measured and where it's called from. In module 11, Practice: real-world use cases, you'll see SQL in its environment: the use cases that come up again and again in any project; the best practices of writing, naming and maintenance that separate a database you can work with from one you're scared to touch; security —SQL injection and how it's really prevented, the permissions and roles this module has been deferring lesson after lesson—; SQL for data analysis, where 10-03's window functions turn into complete reports; and SQL in web development, with ORMs, the connection pool and the N+1 problem. The toolbox is full now; what's left is learning the craft.