PostgreSQL 17 Native JSON_TABLE Deterministic Projection Standard

--- description: PostgreSQL 17 Native JSON_TABLE Deterministic Projection Standard - Mandates the use of PostgreSQL 17 SQL/JSON standard JSON_TABLE for projecting semi-structured payloads into typed relational structures at query and boundary layers. Prohibits runtime JSONB traversal operators and legacy unnesting functions in join and filter paths to eliminate query planner misestimations and runtime deserialization overhead. globs: src/**/*.sql, src/**/*.ts, migrations/**/*.sql, src/repositories/**/*.ts, src/db/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-json-table-deterministic-projection-standard" --- # PostgreSQL 17 Native JSON_TABLE Deterministic Projection Standard > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Runtime JSONB Operator Traversal and Legacy Recordset Deserialization in Hot Query Paths ## System Constraints You are a Principal Database Architect enforcing PostgreSQL 17 SQL/JSON standards. In PostgreSQL 17+, developers must eliminate dynamic runtime JSONB access (->, ->>, #>, jsonb_to_recordset) inside hot analytical, join, and filtering paths. You mandate the use of standard JSON_TABLE with explicit types, strict PATH specifications, and explicit error clauses (ERROR ON ERROR / NULL ON EMPTY) to provide the PostgreSQL cost-based optimizer with accurate statistics, data types, and deterministic single-pass parsing. ## Strict Guidelines - Engineers MUST ALWAYS project semi-structured JSON/JSONB data into relational schemas using standard SQL/JSON 'JSON_TABLE' syntax when extracting multiple attributes or processing arrays. - Engineers MUST NOT use legacy operator chains ('->', '->>', '#>') or legacy set-returning functions ('jsonb_to_recordset', 'jsonb_array_elements') in JOIN, WHERE, or GROUP BY clauses. - Code SHOULD define explicit column types and error-handling semantics ('ERROR ON ERROR' or 'DEFAULT ... ON ERROR') within the 'COLUMNS' clause of 'JSON_TABLE' to prevent silent schema corruption. - Services MUST leverage deterministic 'JSON_TABLE' projections over JSONB columns in ingest pipelines or materialized projections rather than deferred, runtime client-side JSON parsing. - Components NEVER cast dynamically extracted text fields ('(payload->>'field')::numeric') within high-frequency queries due to cardinality misestimation and lack of column statistics. ## Bad vs Good Implementations ### Projecting Nested JSON Array for Joins and Aggregations #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Using jsonb_array_elements with runtime text extraction ('->>') and ad-hoc casting forces PostgreSQL to parse the JSON tree multiple times per row. The optimizer cannot compute single-column statistics or selectivity histograms, frequently choosing suboptimal nested loop plans over large datasets. ```typescript import { sql } from 'kysely'; import { db } from './database'; // ANTI-PATTERN: Using jsonb_array_elements and runtime operator traversal (->>) // Causes repeated deserialization, lacks accurate optimizer statistics, and fails on schema drift. export async function getOrderItemsReport(orderId: string) { return await sql` SELECT o.id AS order_id, (item.val->>'sku')::text AS sku, (item.val->>'quantity')::int AS quantity, (item.val->>'unit_price')::numeric(10, 2) AS unit_price FROM orders o, LATERAL jsonb_array_elements(o.payload->'items') AS item(val) WHERE o.id = ${orderId} AND (item.val->>'quantity')::int > 0 `.execute(db); } ``` #### Production Standard (Correct) > **Standard**: PostgreSQL 17's native JSON_TABLE parses the JSONB payload in a single pass directly into a typed relational table expression. Defining types in the COLUMNS clause allows proper plan cost estimations and prevents silent data conversion failures via explicit 'ERROR ON ERROR' handlers. ```typescript import { sql } from 'kysely'; import { db } from './database'; // PRODUCTION FIX: PostgreSQL 17 native JSON_TABLE with strict schema definition and error boundaries export async function getOrderItemsReport(orderId: string) { return await sql` SELECT o.id AS order_id, jt.sku, jt.quantity, jt.unit_price FROM orders o, JSON_TABLE( o.payload, '$.items[*]' COLUMNS ( sku text PATH '$.sku' ERROR ON ERROR, quantity integer PATH '$.quantity' ERROR ON ERROR, unit_price numeric(10, 2) PATH '$.unit_price' ERROR ON ERROR ) ) AS jt WHERE o.id = ${orderId} AND jt.quantity > 0 `.execute(db); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: PostgreSQL 17 Native JSON_TABLE Deterministic Projection Standard

PostgreSQL 17 Native JSON_TABLE Deterministic Projection Standard defines an industrial-grade behavioral standard for backend engineering. Mandates the use of PostgreSQL 17 SQL/JSON standard JSON_TABLE for projecting semi-structured payloads into typed relational structures at query and boundary layers. Prohibits runtime JSONB traversal operators and legacy unnesting functions in join and filter paths to eliminate query planner misestimations and runtime deserialization overhead. 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: Runtime JSONB Operator Traversal and Legacy Recordset Deserialization in Hot Query Paths 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	Runtime JSONB Operator Traversal and Legacy Recordset Deserialization in Hot Query Paths	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 project semi-structured JSON/JSONB data into relational schemas using standard SQL/JSON 'JSON_TABLE' syntax when extracting multiple attributes or processing arrays.

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.