{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"json-columns","title":"JSON in a column: when it helps and what it costs","url":"https://addaly.com/learn/data-and-sql/json-columns","position":63,"of":66,"minutes":9,"answer":"A JSON column trades every constraint for flexibility, so keys can be misspelt and types can drift on every insert; keep the document, promote the keys you query into generated columns with real types.","keyIdea":"A JSON column trades every constraint for flexibility, so keys can be misspelt and types can drift on every insert; keep the document, promote the keys you query into generated columns with real types, and profile the keys before trusting them."},"body":{"format":"markdown","text":"## Attributes that refuse to be columns\n\nA shop sells shirts, chargers and books. A shirt has a size and a colour; a charger has a wattage and a plug type; a book has an author and a page count. A `products` table with a column for every attribute of every kind of product is mostly NULL and grows a column every time a new kind arrives. A `product_attributes` table with one row per product per attribute works and makes every query a pivot. The third option is a column that holds structured data:\n\n```sql\nCREATE TABLE products (\n  id     bigint PRIMARY KEY,\n  name   text NOT NULL,\n  price  numeric(10,2) NOT NULL,\n  attrs  jsonb NOT NULL DEFAULT '{}'\n);\n\nINSERT INTO products VALUES\n  (1, 'Linen shirt', 1800, '{\"size\": \"M\", \"colour\": \"white\"}'),\n  (2, 'USB-C charger', 1200, '{\"watts\": 65, \"plug\": \"type-D\"}');\n```\n\nPostgres's `jsonb` stores the document in a parsed binary form and lets queries reach inside it: `attrs ->> 'colour'` returns the text `white`, `attrs -> 'watts'` returns the JSON value, and `attrs @> '{\"plug\": \"type-D\"}'` asks whether the document contains that fragment. MySQL has a `JSON` type with `->>` and `JSON_EXTRACT`; SQLite has `json_extract(attrs, '$.colour')`; DuckDB reads JSON natively and can unnest it. BigQuery has a `JSON` type too.\n\nThe other honest use is keeping what an API sent, whole. A `raw_payload jsonb` column beside the parsed columns means that when the parser turns out to have missed a field, the field is still there.\n\n## What you gave up\n\nEvery promise the first lesson of this module made, a JSON column breaks.\n\n**Nothing checks the keys.** Half the shirts say `\"colour\"` and half say `\"color\"`, because two developers wrote two forms, and a query for `attrs ->> 'colour'` finds half the shirts. A table column cannot be misspelt on insert; a JSON key can, on every insert.\n\n**Nothing checks the types.** `\"watts\": 65` in one row and `\"watts\": \"65W\"` in the next. A sum over `(attrs ->> 'watts')::int` fails on the second row, or, if the cast is guarded, silently drops it.\n\n**Nothing checks presence.** A shirt with no size is a valid document.\n\n**The schema has moved into every query.** With columns, the database knows the shape of a product and every tool can display it. With JSON, the shape lives in the heads of the people who wrote the inserts, and the first task of anyone new is to discover it:\n\n```sql\nSELECT key, count(*), count(DISTINCT jsonb_typeof(attrs -> key))\nFROM products, jsonb_object_keys(attrs) AS key\nGROUP BY key ORDER BY count(*) DESC;\n```\n\nThat query lists every key in use, how often, and how many different types it has been given. It is the profiling step from the cleaning module, applied to a column that was supposed to be structured.\n\n## Keep the document, promote what you query\n\nThe pattern that gets the flexibility without the chaos is to keep the JSON and pull the keys you actually filter and join on into real columns, with real types and real constraints:\n\n```sql\nALTER TABLE products\n  ADD COLUMN colour text GENERATED ALWAYS AS (attrs ->> 'colour') STORED,\n  ADD COLUMN watts  int  GENERATED ALWAYS AS ((attrs ->> 'watts')::int) STORED;\nCREATE INDEX ON products (colour);\n```\n\nA generated column is computed from the document on every write, can be indexed like any other column, and fails the insert if the cast fails, which is the type check the JSON did not have. Queries use `colour`; the document remains for the attributes nobody has needed to promote yet. When a key is queried often enough to matter, it is promoted; the JSON column is where attributes wait to be found important.\n\n## Indexing the whole document\n\nPostgres can index a `jsonb` column with a GIN index, after which `attrs @> '{\"plug\": \"type-D\"}'` uses the index rather than scanning every document. It is the right tool for \"find documents containing this fragment\" across unknown keys, and it is large and slow to update, which the performance module's lesson on index types weighs. For a known key, the generated column and its ordinary index are smaller and faster.\n\n## The habit\n\nUse JSON for what varies and for what you want to keep whole. The moment a key appears in a WHERE clause, a JOIN or a GROUP BY, give it a column. And run the key-profiling query on any JSON column you inherit, before believing anything about what it contains."},"check":{"question":"A `products.attrs` JSON column has been written by two services for a year. A query for `attrs ->> 'colour' = 'white'` returns about half the white shirts the merchandising team expects. What is the most likely cause, and how is it found?","options":[{"text":"The jsonb type lower-cases values on storage, so `White` no longer matches; comparing with `ilike` fixes it.","correct":false,"tempting":"jsonb preserves values exactly; it neither lower-cases nor alters text."},{"text":"Half the rows store the document as text rather than jsonb, and the `->>` operator returns NULL for those rows; casting the column to jsonb fixes it.","correct":false,"tempting":"A column has one type; a jsonb column cannot hold some rows as plain text."},{"text":"One service writes the key as `color`, and a query listing the keys in use with `jsonb_object_keys` and their counts will show both spellings.","correct":true},{"text":"The GIN index on `attrs` is stale and returns only rows indexed before the second service started writing; reindexing fixes it.","correct":false,"tempting":"Indexes are updated on every write and cannot return a subset of matching rows; a stale index is not a Postgres failure mode."}]},"prev":{"slug":"views-and-saved-questions","title":"Views: a saved question, not a saved answer","url":"https://addaly.com/learn/data-and-sql/views-and-saved-questions"},"next":{"slug":"history-and-audit-tables","title":"Keeping history: soft deletes, audit rows and who changed what","url":"https://addaly.com/learn/data-and-sql/history-and-audit-tables"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'JSON in a column: when it helps and what it costs', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/json-columns (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"JSON in a column: when it helps and what it costs","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":6,"slug":"changing-data-safely","title":"Changing data safely"},"answer":null,"keyIdea":"A JSON column trades every constraint for flexibility, so keys can be misspelt and types can drift on every insert; keep the document, promote the keys you query into generated columns with real types, and profile the keys before trusting them.","hero":{"kind":"plate","image":null,"alt":null,"caption":null},"blocks":[{"t":"h2","id":"attributes-that-refuse-to-be-columns","runs":[{"text":"Attributes that refuse to be columns"}]},{"t":"p","runs":[{"text":"A shop sells shirts, chargers and books. A shirt has a size and a colour; a charger has a wattage and a plug type; a book has an author and a page count. A "},{"text":"products","code":true},{"text":" table with a column for every attribute of every kind of product is mostly NULL and grows a column every time a new kind arrives. A "},{"text":"product_attributes","code":true},{"text":" table with one row per product per attribute works and makes every query a pivot. The third option is a column that holds structured data:"}]},{"t":"code","lang":"sql","label":"SQL","text":"CREATE TABLE products (\n  id     bigint PRIMARY KEY,\n  name   text NOT NULL,\n  price  numeric(10,2) NOT NULL,\n  attrs  jsonb NOT NULL DEFAULT '{}'\n);\n\nINSERT INTO products VALUES\n  (1, 'Linen shirt', 1800, '{\"size\": \"M\", \"colour\": \"white\"}'),\n  (2, 'USB-C charger', 1200, '{\"watts\": 65, \"plug\": \"type-D\"}');"},{"t":"p","runs":[{"text":"Postgres’s "},{"text":"jsonb","code":true},{"text":" stores the document in a parsed binary form and lets queries reach inside it: "},{"text":"attrs ->> 'colour'","code":true},{"text":" returns the text "},{"text":"white","code":true},{"text":", "},{"text":"attrs -> 'watts'","code":true},{"text":" returns the JSON value, and "},{"text":"attrs @> '{\"plug\": \"type-D\"}'","code":true},{"text":" asks whether the document contains that fragment. MySQL has a "},{"text":"JSON","code":true},{"text":" type with "},{"text":"->>","code":true},{"text":" and "},{"text":"JSON_EXTRACT","code":true},{"text":"; SQLite has "},{"text":"json_extract(attrs, '$.colour')","code":true},{"text":"; DuckDB reads JSON natively and can unnest it. BigQuery has a "},{"text":"JSON","code":true},{"text":" type too."}]},{"t":"p","runs":[{"text":"The other honest use is keeping what an API sent, whole. A "},{"text":"raw_payload jsonb","code":true},{"text":" column beside the parsed columns means that when the parser turns out to have missed a field, the field is still there."}]},{"t":"h2","id":"what-you-gave-up","runs":[{"text":"What you gave up"}]},{"t":"p","runs":[{"text":"Every promise the first lesson of this module made, a JSON column breaks."}]},{"t":"p","runs":[{"text":"Nothing checks the keys.","b":true},{"text":" Half the shirts say "},{"text":"\"colour\"","code":true},{"text":" and half say "},{"text":"\"color\"","code":true},{"text":", because two developers wrote two forms, and a query for "},{"text":"attrs ->> 'colour'","code":true},{"text":" finds half the shirts. A table column cannot be misspelt on insert; a JSON key can, on every insert."}]},{"t":"p","runs":[{"text":"Nothing checks the types.","b":true},{"text":" "},{"text":"\"watts\": 65","code":true},{"text":" in one row and "},{"text":"\"watts\": \"65W\"","code":true},{"text":" in the next. A sum over "},{"text":"(attrs ->> 'watts')::int","code":true},{"text":" fails on the second row, or, if the cast is guarded, silently drops it."}]},{"t":"p","runs":[{"text":"Nothing checks presence.","b":true},{"text":" A shirt with no size is a valid document."}]},{"t":"p","runs":[{"text":"The schema has moved into every query.","b":true},{"text":" With columns, the database knows the shape of a product and every tool can display it. With JSON, the shape lives in the heads of the people who wrote the inserts, and the first task of anyone new is to discover it:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT key, count(*), count(DISTINCT jsonb_typeof(attrs -> key))\nFROM products, jsonb_object_keys(attrs) AS key\nGROUP BY key ORDER BY count(*) DESC;"},{"t":"p","runs":[{"text":"That query lists every key in use, how often, and how many different types it has been given. It is the profiling step from the cleaning module, applied to a column that was supposed to be structured."}]},{"t":"h2","id":"keep-the-document-promote-what-you-query","runs":[{"text":"Keep the document, promote what you query"}]},{"t":"p","runs":[{"text":"The pattern that gets the flexibility without the chaos is to keep the JSON and pull the keys you actually filter and join on into real columns, with real types and real constraints:"}]},{"t":"code","lang":"sql","label":"SQL","text":"ALTER TABLE products\n  ADD COLUMN colour text GENERATED ALWAYS AS (attrs ->> 'colour') STORED,\n  ADD COLUMN watts  int  GENERATED ALWAYS AS ((attrs ->> 'watts')::int) STORED;\nCREATE INDEX ON products (colour);"},{"t":"p","runs":[{"text":"A generated column is computed from the document on every write, can be indexed like any other column, and fails the insert if the cast fails, which is the type check the JSON did not have. Queries use "},{"text":"colour","code":true},{"text":"; the document remains for the attributes nobody has needed to promote yet. When a key is queried often enough to matter, it is promoted; the JSON column is where attributes wait to be found important."}]},{"t":"h2","id":"indexing-the-whole-document","runs":[{"text":"Indexing the whole document"}]},{"t":"p","runs":[{"text":"Postgres can index a "},{"text":"jsonb","code":true},{"text":" column with a GIN index, after which "},{"text":"attrs @> '{\"plug\": \"type-D\"}'","code":true},{"text":" uses the index rather than scanning every document. It is the right tool for “find documents containing this fragment” across unknown keys, and it is large and slow to update, which the performance module’s lesson on index types weighs. For a known key, the generated column and its ordinary index are smaller and faster."}]},{"t":"h2","id":"the-habit","runs":[{"text":"The habit"}]},{"t":"p","runs":[{"text":"Use JSON for what varies and for what you want to keep whole. The moment a key appears in a WHERE clause, a JOIN or a GROUP BY, give it a column. And run the key-profiling query on any JSON column you inherit, before believing anything about what it contains."}]}],"check":{"question":[{"text":"A "},{"text":"products.attrs","code":true},{"text":" JSON column has been written by two services for a year. A query for "},{"text":"attrs ->> 'colour' = 'white'","code":true},{"text":" returns about half the white shirts the merchandising team expects. What is the most likely cause, and how is it found?"}],"options":[{"text":[{"text":"The jsonb type lower-cases values on storage, so "},{"text":"White","code":true},{"text":" no longer matches; comparing with "},{"text":"ilike","code":true},{"text":" fixes it."}],"correct":false,"tempting":[{"text":"jsonb preserves values exactly; it neither lower-cases nor alters text."}]},{"text":[{"text":"Half the rows store the document as text rather than jsonb, and the "},{"text":"->>","code":true},{"text":" operator returns NULL for those rows; casting the column to jsonb fixes it."}],"correct":false,"tempting":[{"text":"A column has one type; a jsonb column cannot hold some rows as plain text."}]},{"text":[{"text":"One service writes the key as "},{"text":"color","code":true},{"text":", and a query listing the keys in use with "},{"text":"jsonb_object_keys","code":true},{"text":" and their counts will show both spellings."}],"correct":true,"tempting":null},{"text":[{"text":"The GIN index on "},{"text":"attrs","code":true},{"text":" is stale and returns only rows indexed before the second service started writing; reindexing fixes it."}],"correct":false,"tempting":[{"text":"Indexes are updated on every write and cannot return a subset of matching rows; a stale index is not a Postgres failure mode."}]}],"order":[0,2,1,3],"why":null},"prev":{"slug":"views-and-saved-questions","title":"Views: a saved question, not a saved answer","minutes":8},"next":{"slug":"history-and-audit-tables","title":"Keeping history: soft deletes, audit rows and who changed what","minutes":9}}}