Postgres 17 JSON_TABLE SQL:2023 Projection Invariant

--- description: Postgres 17 JSON_TABLE SQL:2023 Projection Invariant - Enforces SQL:2023 JSON_TABLE projection over client-side parsing or legacy LATERAL jsonb_array_elements cross-joins for nested JSONB querying. Direct engine-level columnar transformation prevents memory bloat, avoids V8 heap spikes, and leverages native Postgres execution plan optimizations. globs: src/**/*.ts, src/**/*.tsx, src/**/*.sql, migrations/**/*.sql, queries/**/*.sql alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-json-table-projection-invariant" --- # Postgres 17 JSON_TABLE SQL:2023 Projection Invariant > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Client-Side In-Memory JSON Parsing and Chained jsonb_array_elements Tuple Explosion ## System Constraints You are a Principal Software Architect enforcing the Postgres 17 JSON_TABLE SQL:2023 Projection Invariant. Reject all client-side multi-megabyte JSON deserializations and legacy unnesting cascades (such as chained jsonb_array_elements or jsonb_to_recordset). Require JSON_TABLE for mapping nested semi-structured JSONB payloads to strongly typed relational projections at the database engine boundary. ## Strict Guidelines - Engineers MUST ALWAYS use SQL:2023 standard JSON_TABLE syntax when querying, flattening, or projecting nested JSONB structures in PostgreSQL 17+. - Engineers MUST NOT transfer unprojected raw JSONB payloads across the wire into application memory simply to iterate, filter, or map nested elements in the application tier. - Code MUST specify explicit data types and error clauses (e.g., ERROR ON ERROR, EMPTY ON ERROR, or DEFAULT ... ON ERROR) inside the JSON_TABLE COLUMNS clause to prevent silent query corruption. - Services MUST NEVER chain multiple LATERAL jsonb_array_elements() joins for deeply nested lists, as this causes Cartesian intermediate tuple multiplication and server-side memory spikes. - Queries SHOULD project only required relational columns from the JSON document, maintaining strict columnar memory efficiency inside the PostgreSQL executor. ## Bad vs Good Implementations ### Querying Nested JSON Arrays into Relational Rows #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Fetches entire raw JSONB documents over the wire into the application process. De-serializing massive JSON documents into the V8 memory heap leads to high GC pauses, excessive network I/O, and potential Node.js out-of-memory (OOM) crashes under concurrent workloads. ```typescript import { db } from './db'; interface RawOrderRow { id: string; payload: { items: Array<{ sku: string; qty: number; unit_price: number }> }; } export async function getOrderLineItemsBad(orderId: string) { // ANTI-PATTERN: Pulling unbounded raw JSONB documents into Node.js heap memory // Causes severe intermediate serialization latency and V8 GC pressure under load. const query = `SELECT id, payload FROM customer_orders WHERE id = $1`; const result = await db.query<RawOrderRow>(query, [orderId]); return result.rows.flatMap((order) => order.payload.items.map((item) => ({ orderId: order.id, sku: item.sku, quantity: item.qty, totalCost: item.qty * item.unit_price })) ); } ``` #### Production Standard (Correct) > **Standard**: The database executor evaluates the JSONPath expression and constructs typed relational rows directly in Postgres memory before streaming only requested columns to the client, eliminating client memory pressure and leveraging database query optimization. ```typescript import { db } from './db'; interface OrderLineItem { orderId: string; sku: string; quantity: number; totalCost: number; } export async function getOrderLineItemsGood(orderId: string): Promise<OrderLineItem[]> { // INVARIANT: Postgres 17 JSON_TABLE projects nested JSONB directly into typed columns // at the engine layer, avoiding memory bloat and client-side deserialization. const query = ` SELECT o.id AS "orderId", jt.sku, jt.quantity, (jt.quantity * jt.unit_price) AS "totalCost" FROM customer_orders o, JSON_TABLE( o.payload, '$.items[*]' COLUMNS ( sku TEXT PATH '$.sku' ERROR ON ERROR, quantity INTEGER PATH '$.qty' DEFAULT 0 ON ERROR, unit_price NUMERIC(10, 2) PATH '$.unit_price' ERROR ON ERROR ) ) AS jt WHERE o.id = $1; `; const result = await db.query<OrderLineItem>(query, [orderId]); return result.rows; } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 JSON_TABLE SQL:2023 Projection Invariant

Postgres 17 JSON_TABLE SQL:2023 Projection Invariant defines an industrial-grade behavioral standard for backend engineering. Enforces SQL:2023 JSON_TABLE projection over client-side parsing or legacy LATERAL jsonb_array_elements cross-joins for nested JSONB querying. Direct engine-level columnar transformation prevents memory bloat, avoids V8 heap spikes, and leverages native Postgres execution plan optimizations. Modern software architectures require strict adherence to deterministic patterns to guarantee high availability, maintainability, and zero security vulnerabilities.

Framework Paradigm Shift & Drift MechanicsLLMs are trained on millions of open-source repositories containing outdated code, legacy idioms, and unpatched antipatterns. When coding assistants generate implementations for this stack, they default to common statistical tokens: Client-Side In-Memory JSON Parsing and Chained jsonb_array_elements Tuple Explosion Without an explicit system instruction file, developers spend hours debugging silent failures.

Failure Modes & Production Blast Radius

Hazard Analysis: What Breaks Without This Rule

The following failure modes have been cataloged from hundreds of hours of production code reviews where AI assistants produced non-compliant implementations:

Failure Mode	AI Symptom (The Anti-Pattern)	Severity	Production Impact & Remedy
AI Hallucination & Deprecation Drift	Client-Side In-Memory JSON Parsing and Chained jsonb_array_elements Tuple Explosion	Critical	Violating this industrial standard introduces regression bugs, security risks, and unexpected runtime failures in production. Enforce explicit behavioral system directives across all AI coding assistants in your repository.
Silent Architecture Inconsistency	AI assistants produce mixed paradigms across files, creating fragmented codebase conventions.	Medium	Increases cognitive load during code reviews and leads to maintenance debt across engineering teams. Pin project conventions in .cursor/rules, CLAUDE.md, and AGENTS.md so all team agents follow identical guardrails.

Architectural Directives in Detail

Step-by-Step Production Directives

Each rule constraint below is injected into the AI agent's system prompt to establish deterministic boundaries:

Engineers MUST ALWAYS use SQL

Engineers MUST ALWAYS use SQL:2023 standard JSON_TABLE syntax when querying, flattening, or projecting nested JSONB structures in PostgreSQL 17+.

Rationale:AI models frequently generate unconstrained code here because statistical language distributions lack project-specific architectural context. Mandating this directive ensures that generated code adheres to verified industrial production requirements.