Flexible Data in a Column
Modern relational databases let a column hold a JSON document. This is handy for fields that vary by row or evolve over time, such as user settings, without a schema change for every new key.
Binary JSON
Many engines store a parsed binary form rather than raw text. This skips reparsing on every read and supports fast key lookups, though it loses exact whitespace and key order.
Making It Searchable
A plain JSON column has no index on its inner keys, so filtering on a nested field scans every row. To fix this you can:
- Build a generalized inverted index that covers many keys inside the document.
- Create an expression index on one extracted path you query often.
The Tradeoff
JSON columns trade strict schema and constraints for flexibility. Use them for genuinely variable data, but pull stable, frequently queried fields into real columns so the planner can use normal indexes.
Key idea
JSON columns add schema flexibility, but querying nested fields is slow until you add an inverted or expression index on the keys you filter on.