AgentRules.org
RulesStacksClientsAuditorPrunerSelf-HealRFCsBenchmarkCLICI/CDAuthorsSubmit
This specification is maintained by the AgentRules Working Group. Have enhancements or want to claim maintainership?Submit Specification →
backendcritical PriorityID: postgres-17-json-table-streaming-projection

Postgres 17 JSON_TABLE Streaming Relational Projections

Mandates PostgreSQL 17 SQL/JSON standard JSON_TABLE for unpacking large or deeply nested JSONB structures directly into relational tuples. Eliminates intermediate recordset materialization, out-of-memory crashes, and query stalls caused by legacy jsonb_array_elements() and jsonb_to_recordset() lateral cascades.

Prohibited Anti-Pattern: Unbounded Work-Memory Spillage and CPU Throttling via Chained jsonb_array_elements()

Export Ready-to-Drop Configuration

Select format below to copy code or download file directly.

---
description: Postgres 17 JSON_TABLE Streaming Relational Projections - Mandates PostgreSQL 17 SQL/JSON standard JSON_TABLE for unpacking large or deeply nested JSONB structures directly into relational tuples. Eliminates intermediate recordset materialization, out-of-memory crashes, and query stalls caused by legacy jsonb_array_elements() and jsonb_to_recordset() lateral cascades.
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-streaming-projection"
---

# Postgres 17 JSON_TABLE Streaming Relational Projections

> **Severity**: CRITICAL | **Category**: backend
> **Anti-Pattern Warning**: Unbounded Work-Memory Spillage and CPU Throttling via Chained jsonb_array_elements()

## System Constraints
You are a Principal Database Architect and Backend Systems Engineer enforcing the Postgres 17 JSON_TABLE Streaming Projection Standard. You must prohibit legacy JSONB unnesting patterns (such as jsonb_array_elements, jsonb_to_recordset, or LATERAL expansion loops) on large payloads. You must enforce the adoption of SQL/JSON standard JSON_TABLE with explicit typed COLUMNS, NESTED PATH syntax, and deterministic error handling to maintain streaming execution plans and prevent work_mem exhaustion.

## Strict Guidelines
- Engineers MUST ALWAYS use Postgres 17 JSON_TABLE() when projecting tabular rows from JSONB documents containing arrays or hierarchical sub-objects.
- Engineers MUST NOT use jsonb_array_elements(), jsonb_array_elements_text(), or jsonb_to_recordset() in CROSS JOIN LATERAL cascades for ingestion or heavy analytical transforms.
- Engineers MUST define explicit type bindings and error semantics (e.g., ERROR ON ERROR, NULL ON EMPTY, or DEFAULT ... ON ERROR) inside the COLUMNS clause.
- Queries MUST use JSON_TABLE's NESTED PATH syntax to project multi-level nested JSON arrays in a single streaming pass rather than nesting multiple LATERAL joins.
- Code MUST pass raw JSONB parameters directly via parameterized SQL ($1) into JSON_TABLE rather than performing client-side array chunking or application-level string manipulations.
- Database schemas and queries SHOULD rely on jsonpath expression indexes to accelerate JSON_TABLE path evaluation on high-throughput filter criteria.

## Bad vs Good Implementations
### Unpacking Nested Order Payloads into Normalized Projections

#### Bad Antipattern (AI Common Hallucination)
> **Why it fails**: Legacy JSON unnesting via jsonb_array_elements and jsonb_to_recordset constructs intermediate record buffers in memory for every tier of nesting. When handling payloads with thousands of elements, this triggers massive disk spills, excessive garbage collection inside Postgres work_mem, and can crash the backend worker with OOM.
```typescript
import { sql } from 'kysely';
import type { Database } from '../types/db.js';

// ANTI-PATTERN: Chained LATERAL jsonb_array_elements materializes full intermediate
// JSON objects into Postgres memory, causing exponential work_mem allocation
// and severe query degradation on large batch imports.
export async function extractOrderLineItems(db: any, payload: string) {
  return await sql`
    SELECT 
      o.id AS order_id,
      item->>'sku' AS sku,
      (item->>'quantity')::int AS quantity,
      opt->>'name' AS option_name,
      opt->>'value' AS option_value
    FROM jsonb_to_recordset(${payload}::jsonb) AS o(id uuid, items jsonb)
    CROSS JOIN LATERAL jsonb_array_elements(o.items) AS item
    CROSS JOIN LATERAL jsonb_array_elements(item->'options') AS opt
  `.execute(db);
}
```

#### Production Standard (Correct)
> **Standard**: Postgres 17 JSON_TABLE operates directly on the JSONB internal parse tree in a single pass without copying intermediate recordsets. The NESTED PATH clause flattens nested hierarchies with zero lateral join overhead, while deterministic ERROR ON ERROR and DEFAULT ON EMPTY clauses ensure strict data validation.
```typescript
import { sql } from 'kysely';
import type { Database } from '../types/db.js';

// PRODUCTION FIX: Postgres 17 JSON_TABLE streams the relational projection in a single
// pass using NESTED PATH and strongly-typed column mappings with defined error boundaries.
export async function extractOrderLineItems(db: any, payload: string) {
  return await sql`
    SELECT jt.*
    FROM JSON_TABLE(
      ${payload}::jsonb, '$[*]'
      COLUMNS (
        order_id uuid PATH '$.id' ERROR ON ERROR,
        NESTED PATH '$.items[*]'
        COLUMNS (
          sku text PATH '$.sku' ERROR ON ERROR,
          quantity integer PATH '$.quantity' DEFAULT 1 ON EMPTY,
          NESTED PATH '$.options[*]'
          COLUMNS (
            option_name text PATH '$.name',
            option_value text PATH '$.value'
          )
        )
      )
    ) AS jt
  `.execute(db);
}
```
Recommended MCP Servers (mcp-serverlist.com)

Equip agent with system tools to verify code for this stack.

PostgreSQL MCP Server
Enables inspection of PostgreSQL 17 execution plans and EXPLAIN ANALYZE verification of JSON_TABLE projections.
Recommended Agent Skills (askills.dev)

Ready-to-use executable skill functions tailored to this stack.

Postgres Query Plan Visualizer
Verifies that JSONB processing runs via streaming scan nodes rather than materialized cross-join lateral loops.
Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 JSON_TABLE Streaming Relational Projections

Postgres 17 JSON_TABLE Streaming Relational Projections defines an industrial-grade behavioral standard for backend engineering. Mandates PostgreSQL 17 SQL/JSON standard JSON_TABLE for unpacking large or deeply nested JSONB structures directly into relational tuples. Eliminates intermediate recordset materialization, out-of-memory crashes, and query stalls caused by legacy jsonb_array_elements() and jsonb_to_recordset() lateral cascades. 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 Work-Memory Spillage and CPU Throttling via Chained jsonb_array_elements() 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 ModeAI Symptom (The Anti-Pattern)SeverityProduction Impact & Remedy
AI Hallucination & Deprecation DriftUnbounded Work-Memory Spillage and CPU Throttling via Chained jsonb_array_elements()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 InconsistencyAI 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:

1

Engineers MUST ALWAYS use Postgres 17 JSON_TABLE() when projecting tabular rows from JSONB documents containing arrays or hierarchical sub-objects.

Engineers MUST ALWAYS use Postgres 17 JSON_TABLE() when projecting tabular rows from JSONB documents containing arrays or hierarchical sub-objects.

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.
Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.
2

Engineers MUST NOT use jsonb_array_elements(), jsonb_array_elements_text(), or jsonb_to_recordset() in CROSS JOIN LATERAL cascades for ingestion or heavy analytical transforms.

Engineers MUST NOT use jsonb_array_elements(), jsonb_array_elements_text(), or jsonb_to_recordset() in CROSS JOIN LATERAL cascades for ingestion or heavy analytical transforms.

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.
Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.
3

Engineers MUST define explicit type bindings and error semantics (e.g., ERROR ON ERROR, NULL ON EMPTY, or DEFAULT ... ON ERROR) inside the COLUMNS clause.

Engineers MUST define explicit type bindings and error semantics (e.g., ERROR ON ERROR, NULL ON EMPTY, or DEFAULT ... ON ERROR) inside the COLUMNS clause.

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.
Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.
4

Queries MUST use JSON_TABLE's NESTED PATH syntax to project multi-level nested JSON arrays in a single streaming pass rather than nesting multiple LATERAL joins.

Queries MUST use JSON_TABLE's NESTED PATH syntax to project multi-level nested JSON arrays in a single streaming pass rather than nesting multiple LATERAL joins.

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.
Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.
5

Code MUST pass raw JSONB parameters directly via parameterized SQL ($1) into JSON_TABLE rather than performing client-side array chunking or application-level string manipulations.

Code MUST pass raw JSONB parameters directly via parameterized SQL ($1) into JSON_TABLE rather than performing client-side array chunking or application-level string manipulations.

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.
Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.
6

Database schemas and queries SHOULD rely on jsonpath expression indexes to accelerate JSON_TABLE path evaluation on high-throughput filter criteria.

Database schemas and queries SHOULD rely on jsonpath expression indexes to accelerate JSON_TABLE path evaluation on high-throughput filter criteria.

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.
Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.
Technical Questions & Answers

Frequently Asked Technical Questions

Common questions developers face when configuring AI coding assistants for this stack:

Q:Why is Postgres 17 JSON_TABLE Streaming Relational Projections critical for AI coding assistants?

Mandates PostgreSQL 17 SQL/JSON standard JSON_TABLE for unpacking large or deeply nested JSONB structures directly into relational tuples. Eliminates intermediate recordset materialization, out-of-memory crashes, and query stalls caused by legacy jsonb_array_elements() and jsonb_to_recordset() lateral cascades. Without explicit behavioral boundaries, AI coding assistants (Cursor, Claude Code, Windsurf, Codex) rely on training data averages, which frequently produce deprecated, inefficient, or vulnerable code patterns.

Q:How does adopting this rule prevent AI hallucinations?

This rule explicitly declares what the AI must avoid (Unbounded Work-Memory Spillage and CPU Throttling via Chained jsonb_array_elements()) and prescribes verified positive examples. Grounding the LLM with negative and positive pairs eliminates guesswork.

Q:Which AI coding environments are supported?

This specification is multi-platform compatible: Cursor (.cursor/rules/*.mdc & .cursorrules), Anthropic Claude Code (CLAUDE.md), Windsurf (.windsurfrules), Codex / Cline (AGENTS.md), and GitHub Copilot (.github/copilot-instructions.md).

Q:Can I combine this rule with other technology stacks?

Yes. Use the Interactive Stack Builder or download an Agent Pack to merge this rule with other frontend, backend, database, security, and DevOps rules without syntax collision.

Open Specification · CC BY 4.0·Free for Personal, Team & Commercial In-Repo Use

Citation & Open Source Attribution Guidelines

This specification is licensed under Creative Commons Attribution 4.0 International (CC BY 4.0). You are completely free to adapt and share this rule in your workspace. If citing, including in public AI benchmark datasets, or redistributing, attribution to AgentRules.org is required.

Related BACKEND Rules

View all rules →
backend
42

Fastify 5 Schema Compilers & Encapsulation Production Standard

Fastify relies on lexical encapsulation contexts to sandbox decorators, hooks, and compiled validation/serialization schemas without memory leaks or cross-tenant bleed. In Fastify 5, misuse of global compilers or failing to encapsulate plugin boundaries causes compiled `fast-json-stringify` serializers and Ajv validators to either crash on duplicate IDs, leak private data across sibling scopes, or bypass JIT compilation optimizations. Production services must enforce strict encapsulation hierarchies and type-safe schema compilation across every routing subtree.

backend
68

Bun Embedded SQLite WAL Mode & Transaction Synchronization Standard

Production database standard for bun:sqlite. Mandates enabling Write-Ahead Logging (PRAGMA journal_mode = WAL), busy timeouts, and using db.transaction() for atomic operations without lock starvation.

backend
68

Drizzle ORM Batch API & Interactive Transaction Isolation Standard

Production database standard for Drizzle ORM (PostgreSQL & MySQL). Mandates using db.batch() over HTTP drivers to prevent round-trip waterfalls, explicit db.transaction() with serializable/read-committed isolation, and strictly avoids unawaited query promises.

1 Rule in Stack

Postgres