Not all data fits neatly into fixed columns. Event payloads, flexible product attributes, API responses, and user preferences vary in shape from row to row. PostgreSQL's JSONB type lets you store and query semi-structured JSON documents directly in a column, combining the schema flexibility of a document database with the transactions, joins, and indexing of a relational one.
JSONB stores JSON in a decomposed binary form, not as text. This costs a little more on write but makes reads and queries far faster, supports indexing, and removes insignificant whitespace and duplicate keys. The older json type stores the raw text and is rarely the right choice; for anything you will query or index, JSONB is the default.
The power of JSONB is its operators and indexing. A rich set of operators extracts fields, tests containment, and checks for keys, and a GIN index makes containment and key queries fast over millions of documents. Used judiciously — for genuinely variable data, not as an excuse to avoid schema — JSONB is one of PostgreSQL's most distinctive strengths.
Analogy🏏Cricket
🏏 Think of it like cricket: Just as a selection decision can depend on a derived benchmark — 'pick batters whose average exceeds the squad's average', which itself must first be computed — a subquery computes an inner result that the outer query then uses. The insight is that some questions are inherently two-stage: you must establish the benchmark before you can judge against it, and composing queries is how SQL expresses that dependency. Watch the selector actually do it: first he tallies every batter's runs and computes the squad average — that inner computation stands alone, needing nothing from the final decision — and only then does he walk the list judging each player against the number he just derived. That independence is what makes it an uncorrelated subquery: the database can compute the benchmark once, keep it, and reuse it for every row, exactly as the selector does not recompute the squad average per player. The composition also comes in shapes: a benchmark producing one number slots in where a value goes (a scalar subquery in WHERE), while a computed shortlist of qualifying players is itself a table the outer query can select from — a subquery in FROM. Two-stage question, two nested queries, dependency flowing inward-out.
🏏 Showing the Cricket analogy — a Cricket version isn’t available for this concept yet.