SQL/JSON JSON_TABLE PLAN Clause — Technical Analysis

Core Problem

The SQL/JSON standard defines a PLAN clause for JSON_TABLE() that controls how multiple NESTED paths are joined together when producing rows. Without a PLAN clause, the database uses a default strategy (typically OUTER UNION for sibling paths, OUTER for parent-child relationships). The PLAN clause gives users explicit control over whether nested paths produce CROSS joins or UNION joins, and whether INNER or OUTER semantics apply.

PostgreSQL's JSON_TABLE() was committed in v17 without the PLAN clause, which was deferred as a separate feature. This patch aims to add that missing piece of SQL standard compliance.

Architectural Context

How JSON_TABLE Works Internally

JSON_TABLE() is implemented as a table function scan (TableFuncScan) with a custom execution framework in jsonpath_exec.c. The key structures are:

• JsonTablePlan: A node tree representing how paths are evaluated. It includes JsonTablePathScan (for individual path evaluations) and JsonTableSiblingJoin (for combining sibling paths).
• JsonTablePlanState: Runtime state for plan execution, tracking iteration over JSON path results.
• JsonTableExecContext: Top-level execution context holding the root plan state and column plan state references.

The PLAN clause modifies how the JsonTablePlan tree is constructed during parse analysis, which in turn changes the iteration semantics at execution time. Specifically, it controls:

1. CROSS vs UNION for sibling NESTED paths — CROSS produces a cartesian product of rows from sibling paths, while UNION concatenates them (with NULLs for columns not belonging to each path).
2. INNER vs OUTER — INNER suppresses rows where a nested path produces no matches, while OUTER preserves the parent row with NULLs.

Why This Matters

The PLAN clause is significant because:

• It's required by the SQL/JSON standard (SQL:2016 and later)
• It gives users control over potentially exponential row multiplication in complex nested JSON structures
• It enables both cross-product and union semantics that serve different analytical use cases
• No other major RDBMS currently implements it (Oracle notably set it aside), making PostgreSQL a potential leader in this area

Proposed Solution

The patch adds:

1. Grammar changes (gram.y): New syntax for PLAN (path1 CROSS path2), PLAN (path1 UNION path2), PLAN DEFAULT (CROSS/UNION, INNER/OUTER) etc.
2. Parse analysis (parse_jsontable.c): Transforms the PLAN clause into the appropriate JsonTablePlan node tree structure, overriding the default UNION OUTER strategy.
3. Execution changes (jsonpath_exec.c): Modifications to plan state iteration to support the different join semantics specified by the PLAN clause.
4. Deparse support (ruleutils.c / get_json_table()): Ability to reconstruct the PLAN clause in pg_get_viewdef() and similar contexts.

Key Technical Issues Identified in Review

1. Incorrect IsA() Usage on Non-Node Structures

Alexander Korotkov identified that IsA(planstate, JsonTableSiblingJoin) is incorrect because planstate is a JsonTablePlanState (not a Node), so the type tag check via IsA() will not work correctly. The correct form is IsA(planstate->plan, JsonTableSiblingJoin). This bug wasn't caught because test coverage didn't exercise the sibling join branch — a significant gap.

2. Behavior Default Mismatch

The parsing code uses JSON_BEHAVIOR_EMPTY_ARRAY as the default, while the deparsing code (get_json_table()) uses JSON_BEHAVIOR_EMPTY. This inconsistency means round-tripping through deparse/reparse could change semantics — a serious correctness issue.

3. Unnecessary Refactoring Mixed with New Code

Amit Langote's review identified that the patch appeared to revert committed code rather than build on it. Specifically:

• Renaming JsonTableResetRowPattern back to JsonTableResetContextItem
• Removing the isCompositeType() helper function
• Rewriting comments unnecessarily
• Whitespace-only changes

This suggests the patch was adapted from the older pre-commit version of JSON_TABLE rather than being developed incrementally on top of the committed v17 code.

4. Missing Documentation

Both reviewers noted the absence of documentation additions — a blocker for commit.

5. PLAN Clause Always Emitted in Deparse

Korotkov noted that the deparse always emits a PLAN clause even when the user didn't specify one (i.e., defaults apply). The preferred behavior is to omit it when defaults are in effect, to keep view definitions clean and backwards-compatible.

Design Tradeoffs

Reverting vs. Building Forward

The fundamental tension in this patch is between:

• Reverting to pre-commit code: The original JSON_TABLE patch set included PLAN clause support that was stripped before commit. Re-applying that code requires undoing refactoring done during the commit process.
• Building incrementally: Adding PLAN clause support on top of the committed code, touching only what's strictly necessary.

Amit Langote clearly preferred the incremental approach, and the author reworked the patch accordingly, producing a "much more compact" version.

Oracle's Decision to Skip PLAN Clause

The thread notes Oracle set the PLAN clause aside. This is relevant context — it suggests the feature may have limited real-world demand or implementation complexity that other vendors found prohibitive. However, Andrew Dunstan argues it's still worth implementing for standards compliance and utility.

Current Status

As of the latest messages (May 2026), the patch is at v22 and has received substantive review from Alexander Korotkov identifying 8 specific issues. The author has acknowledged these and committed to fixing them. The patch still lacks:

• Documentation
• Adequate test coverage (particularly for sibling join branches)
• pgindent formatting pass
• Fix for the IsA() bug
• Fix for the deparse behavior default mismatch
• Resolution of always-emitting PLAN clause in deparse

The patch appears to be in an active review-revise cycle but is not yet close to commit-ready.