Postgres 17 JSON_TABLE Projection & Nested Path Bounded Guardrail

--- description: Postgres 17 JSON_TABLE Projection & Nested Path Bounded Guardrail - Enforces strict structural constraints, index-backed predicate pushdowns, and nesting boundaries on PostgreSQL 17 JSON_TABLE queries. Unbounded JSON path traversals and unindexed JSON_TABLE expansions cause quadratic memory expansion in work_mem and disk spillages on multi-gigabyte JSONB datasets. All transformations must combine jsonb_path_ops index filtering with bounded path projections to preserve query engine throughput. globs: src/**/*.ts, src/**/*.sql, src/repositories/**/*.ts, migrations/**/*.sql alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-json-table-vectorized-projection-guardrail" --- # Postgres 17 JSON_TABLE Projection & Nested Path Bounded Guardrail > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unbounded JSON_TABLE evaluation scanning raw JSONB without indexed pushdown and Cartesian sibling nesting ## System Constraints You are a Principal Database Architect and Query Engine Specialist enforcing PostgreSQL 17 SQL/JSON standards. You must ensure all JSON_TABLE queries enforce indexed pre-filtering, bounded path depth, explicit data type sizing, and avoid memory-exhaustive Cartesian expansions in NESTED PATH clauses. ## Strict Guidelines - Engineers MUST ALWAYS pair JSON_TABLE with an indexed WHERE clause predicate using the @@ (jsonpath) or @> operator against a GIN (jsonb_path_ops) index to prevent full-table sequential unnesting. - Engineers MUST NOT execute sibling NESTED PATH clauses in a single JSON_TABLE invocation when processing unbounded arrays, as sibling array nesting causes an unconstrained Cartesian product in intermediate work_mem. - Code SHOULD define bounded scalar SQL types (e.g., VARCHAR(255), INTEGER, NUMERIC(12,2)) in the JSON_TABLE COLUMNS clause instead of unbounded TEXT or nested JSONB types unless strictly necessary. - Services MUST explicitly specify the ERROR ON ERROR or EMPTY ON ERROR clause on every column projection to prevent silent scalar casting failures and unpredictable memory buffer allocations. - Components NEVER construct dynamic JSONPath strings via string concatenation; all paths MUST be statically defined or passed via the PASSING clause as typed parameters. ## Bad vs Good Implementations ### Unbounded JSON_TABLE scan with sibling nested paths vs. Index-pushdown bounded projection #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: This query forces PostgreSQL 17 to parse the entire unindexed JSONB payload for every row matching tenant_id. The sibling NESTED PATH constructs produce a Cartesian product between items and tags in memory, multiplying work_mem usage exponentially, and unconstrained TEXT allocations defeat columnar vectorization efficiencies. ```typescript import { sql } from 'kysely'; import { db } from '../database'; // ANTI-PATTERN: Scans whole table, expands unbounded sibling arrays via NESTED PATH // without index pushdown, blowing up query engine work_mem into disk spills. export async function getCustomerOrderMetrics(tenantId: string) { return await sql` SELECT c.id AS customer_id, jt.order_id, jt.item_id, jt.tag FROM customers c, JSON_TABLE( c.metadata, '$.orders[*]' COLUMNS ( order_id BIGINT PATH '$.id', NESTED PATH '$.items[*]' COLUMNS ( item_id TEXT PATH '$.id' ), NESTED PATH '$.tags[*]' COLUMNS ( tag TEXT PATH '$' ) ) ) AS jt WHERE c.tenant_id = ${tenantId}; `.execute(db); } ``` #### Production Standard (Correct) > **Standard**: The optimized query pushes the filter directly to the GIN index via the jsonb_path_ops @@ operator, pruning unviable rows before JSON_TABLE evaluates. It removes sibling array Cartesian multiplication, specifies exact bounded data types (UUID, INTEGER), and defines predictable failure boundaries with EMPTY ON ERROR. ```typescript import { sql } from 'kysely'; import { db } from '../database'; // PRODUCTION FIX: Pre-filters via jsonb_path_ops index with @@ operator, // bounds scalar types, avoids sibling cartesian explosion, and enforces EMPTY ON ERROR. export async function getCustomerOrderMetrics(tenantId: string) { return await sql` SELECT c.id AS customer_id, jt.order_id, jt.item_id FROM customers c CROSS JOIN JSON_TABLE( c.metadata, '$.orders[*] ? (@.status == "completed")' COLUMNS ( order_id BIGINT PATH '$.id' ERROR ON ERROR, NESTED PATH '$.items[*]' COLUMNS ( item_id UUID PATH '$.id' EMPTY ON ERROR, quantity INTEGER PATH '$.qty' EMPTY ON ERROR ) ) ) AS jt WHERE c.tenant_id = ${tenantId} -- SARGable predicate utilizing GIN index (metadata jsonb_path_ops) AND c.metadata @@ '$.orders[*].status == "completed"'; `.execute(db); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 JSON_TABLE Projection & Nested Path Bounded Guardrail

Postgres 17 JSON_TABLE Projection & Nested Path Bounded Guardrail defines an industrial-grade behavioral standard for backend engineering. Enforces strict structural constraints, index-backed predicate pushdowns, and nesting boundaries on PostgreSQL 17 JSON_TABLE queries. Unbounded JSON path traversals and unindexed JSON_TABLE expansions cause quadratic memory expansion in work_mem and disk spillages on multi-gigabyte JSONB datasets. All transformations must combine jsonb_path_ops index filtering with bounded path projections to preserve query engine throughput. 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: Unbounded JSON_TABLE evaluation scanning raw JSONB without indexed pushdown and Cartesian sibling nesting 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	Unbounded JSON_TABLE evaluation scanning raw JSONB without indexed pushdown and Cartesian sibling nesting	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 pair JSON_TABLE with an indexed WHERE clause predicate using the @@ (jsonpath) or @> operator against a GIN (jsonb_path_ops) index to prevent full-table sequential unnesting.

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.