Modern relational databases can handle semi-structured data without abandoning the relational model, making JSON particularly useful when part of a schema is genuinely variable. JSON works well for sparse category-specific attributes, external API or webhook payloads, and audit snapshots that need to preserve an original document. However, core entities that are regularly joined, constrained, filtered, sorted, or aggregated are generally better represented by typed relational columns. Putting an entire application schema into a single JSON document sacrifices constraints, data types, and useful planner statistics, while repeated extraction and casting can add unnecessary processing overhead. The practical approach is therefore to use JSON for the variable portion of a model while keeping frequently queried business data relational.
For PostgreSQL workloads, jsonb is generally the preferred type when JSON needs to be queried. Unlike plain json, which preserves the original textual representation, jsonb stores parsed binary data and supports indexing and containment operations. The deck’s extraction model is built around an important distinction: -> returns a JSON/JSONB value that can be navigated further, while ->> extracts text for comparison or casting. Path operators such as #> and #>> provide another way to reach nested values. JSON arrays can also be expanded into relational rows with functions such as JSONB_ARRAY_ELEMENTS, allowing nested items to be grouped, aggregated, and analysed like ordinary table data. PostgreSQL’s documentation confirms these extraction operators and JSONB-specific querying capabilities.
JSONB becomes especially powerful when filtering and indexing are designed around the actual access pattern. Containment with @> and key-existence operators such as ?, ?|, and ?& can be accelerated with GIN indexes, while expression indexes are useful when one extracted field is queried repeatedly. PostgreSQL provides both the default jsonb_ops GIN operator class and the more specialized jsonb_path_ops; the latter supports fewer operators but can provide better performance for supported containment and JSON-path workloads. The deck also highlights partial indexes for frequently queried subsets of data. The important principle is that indexing JSON is not simply about adding a GIN index everywhere: the index type should match the predicates the application actually executes.
JSON can also be generated directly from relational data, allowing databases to construct nested API responses using functions such as JSONB_BUILD_OBJECT and JSONB_AGG. At the modelling layer, the deck recommends promoting a JSON attribute to a typed column when it becomes a regular filtering, joining, constraint, sorting, or aggregation target. Generated columns can provide a practical bridge by extracting a frequently queried JSON value into a typed, indexable field while retaining the original document. This approach also addresses an important performance issue: repeated JSON extraction and casting consumes CPU, large documents may require additional storage and decompression work, and JSON predicates can provide weaker planner statistics than ordinary typed columns.
The central lesson is to treat JSON as a deliberate modelling tool rather than a replacement for relational design. Avoid confusing -> with ->>, compare numeric JSON values only after appropriate casting, index frequently queried keys, avoid putting frequently updated counters inside JSONB when whole-row rewrites would become expensive, and remember that JSONB_AGG returns NULL for an empty input unless it is wrapped with COALESCE. A robust production pattern is to retain raw webhook or external payloads, promote frequently accessed fields into generated or typed columns, expand nested arrays only when analytical queries require them, and aggregate the resulting rows at the appropriate grain. In this hybrid model, relational columns provide structure, constraints, statistics, and efficient access, while JSONB provides the flexibility needed for genuinely variable data.
DECISION STATS 