JSON in a relational database
Stage 0 called out convergence - relational databases absorbing features that once needed a separate store. The clearest example: a relational table can hold a JSON document in a column and query inside it. You get document-style flexibility without leaving SQL or giving up ACID. PostgreSQL has the jsonb type; SQLite has JSON functions (used below); MySQL and others have JSON too.
Storing JSON
Put semi-structured or variable data in a JSON (text) column, alongside normal typed columns:
Querying inside JSON
Reach into a document with json_extract(doc, '$.path'), or the shorthand operators -> (returns JSON) and ->> (returns a plain value):
$.color is a path into the document; $.sizes[0] indexes into an array. Filter on it just like a column: WHERE attrs ->> 'color' = 'blue'.
Expanding arrays
A JSON array can be turned into rows with the table-valued json_each, so you can join or aggregate over nested data:
Indexing and when to use it
JSON querying can be slow because the database parses the document each time. Engines fix this differently: PostgreSQL indexes jsonb with a GIN index (or indexes a value extracted into a generated column); SQLite indexes an expression like json_extract(attrs,'$.color'). Index the JSON paths you filter on, just as you would a column.
The judgement call - which echoes NoSQL modeling:
- Real columns for data that is structured and queried often - you get types, constraints, and easy indexes.
- A JSON column for genuinely variable or sparse attributes that differ per row, or rarely-queried blobs.
- A document database when the whole application is document-shaped, not just a corner of it.
Reaching for a JSON column for everything throws away the relational guarantees you spent Stage 2 learning - use it deliberately.
Try it
Finish the query so it returns the name and color of every blue item. The setup is included - change only the WHERE, then press Run for an instant verdict.
Show the answer
Filter on the extracted value just like a column: WHERE attrs ->> 'color' = 'blue'. It returns the running shoe and the rain jacket, both blue.
Quick quiz
JSON in relational
3 questions1What does the ->> operator return when reading a JSON field?
2When is a JSON column the right choice over normal columns?
3Why can querying JSON be slow, and how is it addressed?
That rounds out Stage 5. Stage 6 - Scaling and Advanced is the final stretch: replication, sharding, consistency, and vector search.