JSONB and semi-structured data — SQL & Relational Databases

JSONB is PostgreSQL's binary JSON storage type — it stores JSON documents in a decomposed binary format that enables efficient indexing, containment queries, and key-based access without re-parsing the JSON string on every query. Unlike the JSON type (which stores JSON as text and re-parses on every access), JSONB pre-processes the JSON at insert time: it deduplicates keys, discards duplicate key-value pairs keeping only the last occurrence, and sorts key-value pairs for fast binary search. This pre-processing makes JSONB key access approximately 10–100x faster than the equivalent JSON type access for documents with many keys.

In data engineering, JSONB handles schema-on-read data — attributes that vary across entities, change frequently in structure, or arrive from external APIs without a fixed schema. Player performance attributes that differ by format (T20 power-play strike rate, Test session average), match event metadata from streaming APIs, and configurable pipeline parameters stored alongside pipeline run records are well-suited JSONB candidates. JSONB allows a flexible schema for variable, optional attributes alongside the rigid relational structure of the normalised tables — complementing rather than replacing relational design.

The key design decision is when to use JSONB versus when to add a relational column. Relational columns are correct when the attribute is required (NOT NULL), must be indexed for range queries, appears in GROUP BY aggregations, or needs type-enforced validation. JSONB is correct when the set of attributes is open-ended and evolving, attributes vary by entity subtype, data arrives as JSON from an external source whose structure is not fully known, or flexible ad-hoc queries on arbitrary attributes are needed. Good schema design uses both — normalised relational columns for structured facts and JSONB for the flexible remainder.

Analogy🏏Cricket

🏏 Think of it like cricket: A SELECT query is precisely how a selection committee picks a playing XI. FROM is the full list of centrally contracted players — the raw pool. WHERE is the fitness and eligibility screen: injured or unavailable players are removed before anyone debates merit, and the fewer names that survive this screen, the faster the meeting goes — exactly why a good WHERE clause matters more than anything downstream. ORDER BY is ranking the survivors by recent form, then by experience as the tiebreaker. LIMIT 11 takes the top of that ranked list and stops. The committee never ranks the entire national player pool and then discards thousands of names — and neither should your query force the database to sort millions of rows it will immediately throw away. The order of operations is the whole game: filter first, sort what remains, take only what you need.