diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c index 89e66de..fa8037f 100644 --- a/contrib/pg_stat_statements/pg_stat_statements.c +++ b/contrib/pg_stat_statements/pg_stat_statements.c @@ -2923,9 +2923,11 @@ JumbleExpr(pgssJumbleState *jstate, Node *node) { TableFunc *tablefunc = (TableFunc *) node; + APP_JUMB(tablefunc->functype); JumbleExpr(jstate, tablefunc->docexpr); JumbleExpr(jstate, tablefunc->rowexpr); JumbleExpr(jstate, (Node *) tablefunc->colexprs); + JumbleExpr(jstate, (Node *) tablefunc->colvalexprs); } break; case T_TableSampleClause: diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index 900fa74..2f0d440 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -2842,7 +2842,9 @@ ExplainTargetRel(Plan *plan, Index rti, ExplainState *es) break; case T_TableFuncScan: Assert(rte->rtekind == RTE_TABLEFUNC); - objectname = "xmltable"; + objectname = rte->tablefunc ? + rte->tablefunc->functype == TFT_XMLTABLE ? + "xmltable" : "json_table" : NULL; objecttag = "Table Function Name"; break; case T_ValuesScan: diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c index d410e54..13bd16c 100644 --- a/src/backend/executor/execExpr.c +++ b/src/backend/executor/execExpr.c @@ -2184,6 +2184,7 @@ ExecInitExprRec(Expr *node, ExprState *state, var->var.cb_arg = var; var->estate = ExecInitExpr(argexpr, state->parent); var->econtext = NULL; + var->mcxt = NULL; var->evaluated = false; var->value = (Datum) 0; var->isnull = true; diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c index d842670..e94c24f 100644 --- a/src/backend/executor/execExprInterp.c +++ b/src/backend/executor/execExprInterp.c @@ -4122,7 +4122,7 @@ ExecEvalAggOrderedTransTuple(ExprState *state, ExprEvalStep *op, /* * Evaluate a expression substituting specified value in its CaseTestExpr nodes. */ -static Datum +Datum ExecEvalExprPassingCaseValue(ExprState *estate, ExprContext *econtext, bool *isnull, Datum caseval_datum, bool caseval_isnull) @@ -4182,6 +4182,7 @@ ExecEvalJsonBehavior(ExprContext *econtext, JsonBehavior *behavior, case JSON_BEHAVIOR_NULL: case JSON_BEHAVIOR_UNKNOWN: + case JSON_BEHAVIOR_EMPTY: *is_null = true; return (Datum) 0; @@ -4243,8 +4244,14 @@ EvalJsonPathVar(void *cxt, bool *isnull) if (!ecxt->evaluated) { + MemoryContext oldcxt = ecxt->mcxt ? + MemoryContextSwitchTo(ecxt->mcxt) : NULL; + ecxt->value = ExecEvalExpr(ecxt->estate, ecxt->econtext, &ecxt->isnull); ecxt->evaluated = true; + + if (oldcxt) + MemoryContextSwitchTo(oldcxt); } *isnull = ecxt->isnull; @@ -4432,6 +4439,11 @@ ExecEvalJsonExpr(ExprState *state, ExprEvalStep *op, ExprContext *econtext, *resnull = false; break; + case IS_JSON_TABLE: + res = item; + *resnull = false; + break; + default: elog(ERROR, "unrecognized SQL/JSON expression op %d", jexpr->op); @@ -4452,6 +4464,7 @@ ExecEvalJsonExpr(ExprState *state, ExprEvalStep *op, ExprContext *econtext, } if (jexpr->op != IS_JSON_EXISTS && + jexpr->op != IS_JSON_TABLE && (!empty ? jexpr->op != IS_JSON_VALUE : /* result is already coerced in DEFAULT behavior case */ jexpr->on_empty.btype != JSON_BEHAVIOR_DEFAULT)) diff --git a/src/backend/executor/nodeTableFuncscan.c b/src/backend/executor/nodeTableFuncscan.c index fed6f2b..dfae539 100644 --- a/src/backend/executor/nodeTableFuncscan.c +++ b/src/backend/executor/nodeTableFuncscan.c @@ -28,6 +28,7 @@ #include "executor/tablefunc.h" #include "miscadmin.h" #include "utils/builtins.h" +#include "utils/jsonpath.h" #include "utils/lsyscache.h" #include "utils/memutils.h" #include "utils/xml.h" @@ -161,8 +162,9 @@ ExecInitTableFuncScan(TableFuncScan *node, EState *estate, int eflags) scanstate->ss.ps.qual = ExecInitQual(node->scan.plan.qual, &scanstate->ss.ps); - /* Only XMLTABLE is supported currently */ - scanstate->routine = &XmlTableRoutine; + /* Only XMLTABLE and JSON_TABLE are supported currently */ + scanstate->routine = + tf->functype == TFT_XMLTABLE ? &XmlTableRoutine : &JsonbTableRoutine; scanstate->perValueCxt = AllocSetContextCreate(CurrentMemoryContext, @@ -365,14 +367,17 @@ tfuncInitialize(TableFuncScanState *tstate, ExprContext *econtext, Datum doc) routine->SetNamespace(tstate, ns_name, ns_uri); } - /* Install the row filter expression into the table builder context */ - value = ExecEvalExpr(tstate->rowexpr, econtext, &isnull); - if (isnull) - ereport(ERROR, - (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED), - errmsg("row filter expression must not be null"))); + if (routine->SetRowFilter) + { + /* Install the row filter expression into the table builder context */ + value = ExecEvalExpr(tstate->rowexpr, econtext, &isnull); + if (isnull) + ereport(ERROR, + (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED), + errmsg("row filter expression must not be null"))); - routine->SetRowFilter(tstate, TextDatumGetCString(value)); + routine->SetRowFilter(tstate, TextDatumGetCString(value)); + } /* * Install the column filter expressions into the table builder context. diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index 0171388..1386831 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -1234,6 +1234,7 @@ _copyTableFunc(const TableFunc *from) { TableFunc *newnode = makeNode(TableFunc); + COPY_SCALAR_FIELD(functype); COPY_NODE_FIELD(ns_uris); COPY_NODE_FIELD(ns_names); COPY_NODE_FIELD(docexpr); @@ -1244,7 +1245,9 @@ _copyTableFunc(const TableFunc *from) COPY_NODE_FIELD(colcollations); COPY_NODE_FIELD(colexprs); COPY_NODE_FIELD(coldefexprs); + COPY_NODE_FIELD(colvalexprs); COPY_BITMAPSET_FIELD(notnulls); + COPY_NODE_FIELD(plan); COPY_SCALAR_FIELD(ordinalitycol); COPY_LOCATION_FIELD(location); @@ -2451,6 +2454,99 @@ _copyJsonArgument(const JsonArgument *from) return newnode; } +/* + * _copyJsonTable + */ +static JsonTable * +_copyJsonTable(const JsonTable *from) +{ + JsonTable *newnode = makeNode(JsonTable); + + COPY_NODE_FIELD(common); + COPY_NODE_FIELD(columns); + COPY_NODE_FIELD(plan); + COPY_NODE_FIELD(on_error); + COPY_NODE_FIELD(alias); + COPY_SCALAR_FIELD(location); + + return newnode; +} + +/* + * _copyJsonTableColumn + */ +static JsonTableColumn * +_copyJsonTableColumn(const JsonTableColumn *from) +{ + JsonTableColumn *newnode = makeNode(JsonTableColumn); + + COPY_SCALAR_FIELD(coltype); + COPY_STRING_FIELD(name); + COPY_NODE_FIELD(typename); + COPY_STRING_FIELD(pathspec); + COPY_STRING_FIELD(pathname); + COPY_SCALAR_FIELD(format); + COPY_SCALAR_FIELD(wrapper); + COPY_SCALAR_FIELD(omit_quotes); + COPY_NODE_FIELD(columns); + COPY_NODE_FIELD(on_empty); + COPY_NODE_FIELD(on_error); + COPY_SCALAR_FIELD(location); + + return newnode; +} + +/* + * _copyJsonTablePlan + */ +static JsonTablePlan * +_copyJsonTablePlan(const JsonTablePlan *from) +{ + JsonTablePlan *newnode = makeNode(JsonTablePlan); + + COPY_SCALAR_FIELD(plan_type); + COPY_SCALAR_FIELD(join_type); + COPY_STRING_FIELD(pathname); + COPY_NODE_FIELD(plan1); + COPY_NODE_FIELD(plan2); + COPY_SCALAR_FIELD(location); + + return newnode; +} + +/* + * _copyJsonTableParentNode + */ +static JsonTableParentNode * +_copyJsonTableParentNode(const JsonTableParentNode *from) +{ + JsonTableParentNode *newnode = makeNode(JsonTableParentNode); + + COPY_NODE_FIELD(path); + COPY_STRING_FIELD(name); + COPY_NODE_FIELD(child); + COPY_SCALAR_FIELD(outerJoin); + COPY_SCALAR_FIELD(colMin); + COPY_SCALAR_FIELD(colMax); + + return newnode; +} + +/* + * _copyJsonTableSiblingNode + */ +static JsonTableSiblingNode * +_copyJsonTableSiblingNode(const JsonTableSiblingNode *from) +{ + JsonTableSiblingNode *newnode = makeNode(JsonTableSiblingNode); + + COPY_NODE_FIELD(larg); + COPY_NODE_FIELD(rarg); + COPY_SCALAR_FIELD(cross); + + return newnode; +} + /* **************************************************************** * relation.h copy functions * @@ -5395,6 +5491,21 @@ copyObjectImpl(const void *from) case T_JsonItemCoercions: retval = _copyJsonItemCoercions(from); break; + case T_JsonTable: + retval = _copyJsonTable(from); + break; + case T_JsonTableColumn: + retval = _copyJsonTableColumn(from); + break; + case T_JsonTablePlan: + retval = _copyJsonTablePlan(from); + break; + case T_JsonTableParentNode: + retval = _copyJsonTableParentNode(from); + break; + case T_JsonTableSiblingNode: + retval = _copyJsonTableSiblingNode(from); + break; /* * RELATION NODES diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index c0ce2d2..b8ce7a2 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -119,6 +119,7 @@ _equalRangeVar(const RangeVar *a, const RangeVar *b) static bool _equalTableFunc(const TableFunc *a, const TableFunc *b) { + COMPARE_SCALAR_FIELD(functype); COMPARE_NODE_FIELD(ns_uris); COMPARE_NODE_FIELD(ns_names); COMPARE_NODE_FIELD(docexpr); @@ -129,7 +130,9 @@ _equalTableFunc(const TableFunc *a, const TableFunc *b) COMPARE_NODE_FIELD(colcollations); COMPARE_NODE_FIELD(colexprs); COMPARE_NODE_FIELD(coldefexprs); + COMPARE_NODE_FIELD(colvalexprs); COMPARE_BITMAPSET_FIELD(notnulls); + COMPARE_NODE_FIELD(plan); COMPARE_SCALAR_FIELD(ordinalitycol); COMPARE_LOCATION_FIELD(location); @@ -137,6 +140,29 @@ _equalTableFunc(const TableFunc *a, const TableFunc *b) } static bool +_equalJsonTableParentNode(const JsonTableParentNode *a, const JsonTableParentNode *b) +{ + COMPARE_NODE_FIELD(path); + COMPARE_STRING_FIELD(name); + COMPARE_NODE_FIELD(child); + COMPARE_SCALAR_FIELD(outerJoin); + COMPARE_SCALAR_FIELD(colMin); + COMPARE_SCALAR_FIELD(colMax); + + return true; +} + +static bool +_equalJsonTableSiblingNode(const JsonTableSiblingNode *a, const JsonTableSiblingNode *b) +{ + COMPARE_NODE_FIELD(larg); + COMPARE_NODE_FIELD(rarg); + COMPARE_SCALAR_FIELD(cross); + + return true; +} + +static bool _equalIntoClause(const IntoClause *a, const IntoClause *b) { COMPARE_NODE_FIELD(rel); @@ -3293,6 +3319,12 @@ equal(const void *a, const void *b) case T_JsonItemCoercions: retval = _equalJsonItemCoercions(a, b); break; + case T_JsonTableParentNode: + retval = _equalJsonTableParentNode(a, b); + break; + case T_JsonTableSiblingNode: + retval = _equalJsonTableSiblingNode(a, b); + break; /* * RELATION NODES diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c index ebc41ea..334cabe 100644 --- a/src/backend/nodes/makefuncs.c +++ b/src/backend/nodes/makefuncs.c @@ -661,6 +661,25 @@ makeJsonBehavior(JsonBehaviorType type, Node *default_expr) } /* + * makeJsonTableJoinedPlan - + * creates a joined JsonTablePlan node + */ +Node * +makeJsonTableJoinedPlan(JsonTablePlanJoinType type, Node *plan1, Node *plan2, + int location) +{ + JsonTablePlan *n = makeNode(JsonTablePlan); + + n->plan_type = JSTP_JOINED; + n->join_type = type; + n->plan1 = castNode(JsonTablePlan, plan1); + n->plan2 = castNode(JsonTablePlan, plan2); + n->location = location; + + return (Node *) n; +} + +/* * makeJsonEncoding - * converts JSON encoding name to enum JsonEncoding */ diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c index 79cb602..76af07d 100644 --- a/src/backend/nodes/nodeFuncs.c +++ b/src/backend/nodes/nodeFuncs.c @@ -2280,6 +2280,8 @@ expression_tree_walker(Node *node, return true; if (walker(tf->coldefexprs, context)) return true; + if (walker(tf->colvalexprs, context)) + return true; } break; case T_JsonValueExpr: @@ -3147,6 +3149,7 @@ expression_tree_mutator(Node *node, MUTATE(newnode->rowexpr, tf->rowexpr, Node *); MUTATE(newnode->colexprs, tf->colexprs, List *); MUTATE(newnode->coldefexprs, tf->coldefexprs, List *); + MUTATE(newnode->colvalexprs, tf->colvalexprs, List *); return (Node *) newnode; } break; @@ -3968,6 +3971,30 @@ raw_expression_tree_walker(Node *node, return true; } break; + case T_JsonTable: + { + JsonTable *jt = (JsonTable *) node; + + if (walker(jt->common, context)) + return true; + if (walker(jt->columns, context)) + return true; + } + break; + case T_JsonTableColumn: + { + JsonTableColumn *jtc = (JsonTableColumn *) node; + + if (walker(jtc->typename, context)) + return true; + if (walker(jtc->on_empty, context)) + return true; + if (walker(jtc->on_error, context)) + return true; + if (jtc->coltype == JTC_NESTED && walker(jtc->columns, context)) + return true; + } + break; default: elog(ERROR, "unrecognized node type: %d", (int) nodeTag(node)); diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c index ca2de9f..21a9ec8 100644 --- a/src/backend/nodes/outfuncs.c +++ b/src/backend/nodes/outfuncs.c @@ -1054,6 +1054,7 @@ _outTableFunc(StringInfo str, const TableFunc *node) { WRITE_NODE_TYPE("TABLEFUNC"); + WRITE_ENUM_FIELD(functype, TableFuncType); WRITE_NODE_FIELD(ns_uris); WRITE_NODE_FIELD(ns_names); WRITE_NODE_FIELD(docexpr); @@ -1064,7 +1065,9 @@ _outTableFunc(StringInfo str, const TableFunc *node) WRITE_NODE_FIELD(colcollations); WRITE_NODE_FIELD(colexprs); WRITE_NODE_FIELD(coldefexprs); + WRITE_NODE_FIELD(colvalexprs); WRITE_BITMAPSET_FIELD(notnulls); + WRITE_NODE_FIELD(plan); WRITE_INT_FIELD(ordinalitycol); WRITE_LOCATION_FIELD(location); } @@ -1805,6 +1808,29 @@ _outJsonIsPredicateOpts(StringInfo str, const JsonIsPredicateOpts *node) WRITE_BOOL_FIELD(unique_keys); } +static void +_outJsonTableParentNode(StringInfo str, const JsonTableParentNode *node) +{ + WRITE_NODE_TYPE("JSONTABPNODE"); + + WRITE_NODE_FIELD(path); + WRITE_STRING_FIELD(name); + WRITE_NODE_FIELD(child); + WRITE_BOOL_FIELD(outerJoin); + WRITE_INT_FIELD(colMin); + WRITE_INT_FIELD(colMax); +} + +static void +_outJsonTableSiblingNode(StringInfo str, const JsonTableSiblingNode *node) +{ + WRITE_NODE_TYPE("JSONTABSNODE"); + + WRITE_NODE_FIELD(larg); + WRITE_NODE_FIELD(rarg); + WRITE_BOOL_FIELD(cross); +} + /***************************************************************************** * * Stuff from relation.h. @@ -4375,6 +4401,12 @@ outNode(StringInfo str, const void *obj) case T_JsonItemCoercions: _outJsonItemCoercions(str, obj); break; + case T_JsonTableParentNode: + _outJsonTableParentNode(str, obj); + break; + case T_JsonTableSiblingNode: + _outJsonTableSiblingNode(str, obj); + break; default: diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c index 620c878..2f787f2 100644 --- a/src/backend/nodes/readfuncs.c +++ b/src/backend/nodes/readfuncs.c @@ -479,6 +479,7 @@ _readTableFunc(void) { READ_LOCALS(TableFunc); + READ_ENUM_FIELD(functype, TableFuncType); READ_NODE_FIELD(ns_uris); READ_NODE_FIELD(ns_names); READ_NODE_FIELD(docexpr); @@ -489,7 +490,9 @@ _readTableFunc(void) READ_NODE_FIELD(colcollations); READ_NODE_FIELD(colexprs); READ_NODE_FIELD(coldefexprs); + READ_NODE_FIELD(colvalexprs); READ_BITMAPSET_FIELD(notnulls); + READ_NODE_FIELD(plan); READ_INT_FIELD(ordinalitycol); READ_LOCATION_FIELD(location); @@ -1403,6 +1406,33 @@ _readJsonExpr(void) READ_DONE(); } +static JsonTableParentNode * +_readJsonTableParentNode(void) +{ + READ_LOCALS(JsonTableParentNode); + + READ_NODE_FIELD(path); + READ_STRING_FIELD(name); + READ_NODE_FIELD(child); + READ_BOOL_FIELD(outerJoin); + READ_INT_FIELD(colMin); + READ_INT_FIELD(colMax); + + READ_DONE(); +} + +static JsonTableSiblingNode * +_readJsonTableSiblingNode(void) +{ + READ_LOCALS(JsonTableSiblingNode); + + READ_NODE_FIELD(larg); + READ_NODE_FIELD(rarg); + READ_BOOL_FIELD(cross); + + READ_DONE(); +} + /* * _readJsonCoercion */ @@ -2822,6 +2852,10 @@ parseNodeString(void) return_value = _readJsonCoercion(); else if (MATCH("JSONITEMCOERCIONS", 17)) return_value = _readJsonItemCoercions(); + else if (MATCH("JSONTABPNODE", 12)) + return_value = _readJsonTableParentNode(); + else if (MATCH("JSONTABSNODE", 12)) + return_value = _readJsonTableSiblingNode(); else { elog(ERROR, "badly formatted node string \"%.32s\"...", token); diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 4210296..df20def 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -611,9 +611,29 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); json_object_aggregate_constructor json_array_aggregate_constructor json_path_specification + json_table + json_table_column_definition + json_table_ordinality_column_definition + json_table_regular_column_definition + json_table_formatted_column_definition + json_table_nested_columns + json_table_plan_clause_opt + json_table_specific_plan + json_table_plan + json_table_plan_simple + json_table_plan_parent_child + json_table_plan_outer + json_table_plan_inner + json_table_plan_sibling + json_table_plan_union + json_table_plan_cross + json_table_plan_primary + json_table_default_plan %type json_arguments json_passing_clause_opt + json_table_columns_clause + json_table_column_definition_list json_name_and_value_list json_value_expr_list json_array_aggregate_order_by_clause_opt @@ -622,9 +642,13 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type json_table_path_name json_as_path_name_clause_opt + json_table_column_path_specification_clause_opt %type json_encoding json_encoding_clause_opt + json_table_default_plan_choices + json_table_default_plan_inner_outer + json_table_default_plan_union_cross json_wrapper_clause_opt json_wrapper_behavior json_conditional_or_unconditional_opt @@ -638,6 +662,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); json_behavior_true json_behavior_false json_behavior_unknown + json_behavior_empty json_behavior_empty_array json_behavior_empty_object json_behavior_default @@ -645,6 +670,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); json_query_behavior json_exists_error_behavior json_exists_error_clause_opt + json_table_error_behavior + json_table_error_clause_opt %type json_value_on_behavior_clause_opt json_query_on_behavior_clause_opt @@ -716,7 +743,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); INTERSECT INTERVAL INTO INVOKER IS ISNULL ISOLATION JOIN JSON JSON_ARRAY JSON_ARRAYAGG JSON_EXISTS JSON_OBJECT JSON_OBJECTAGG - JSON_QUERY JSON_VALUE + JSON_QUERY JSON_TABLE JSON_VALUE KEY KEYS KEEP @@ -726,7 +753,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); MAPPING MATCH MATERIALIZED MAXVALUE METHOD MINUTE_P MINVALUE MODE MONTH_P MOVE - NAME_P NAMES NATIONAL NATURAL NCHAR NEW NEXT NO NONE + NAME_P NAMES NATIONAL NATURAL NCHAR NESTED NEW NEXT NO NONE NOT NOTHING NOTIFY NOTNULL NOWAIT NULL_P NULLIF NULLS_P NUMERIC @@ -734,7 +761,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); ORDER ORDINALITY OTHERS OUT_P OUTER_P OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER - PARALLEL PARSER PARTIAL PARTITION PASSING PASSWORD PLACING PLANS POLICY + PARALLEL PARSER PARTIAL PARTITION PASSING PASSWORD PATH PLACING PLAN PLANS POLICY POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY PRIOR PRIVILEGES PROCEDURAL PROCEDURE PROCEDURES PROGRAM PUBLICATION @@ -825,7 +852,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); */ %nonassoc UNBOUNDED /* ideally should have same precedence as IDENT */ %nonassoc ERROR_P EMPTY_P DEFAULT ABSENT /* JSON error/empty behavior */ -%nonassoc FALSE_P KEEP OMIT PASSING TRUE_P UNKNOWN +%nonassoc COLUMNS FALSE_P KEEP OMIT PASSING TRUE_P UNKNOWN %nonassoc IDENT GENERATED NULL_P PARTITION RANGE ROWS GROUPS PRECEDING FOLLOWING CUBE ROLLUP %left Op OPERATOR /* multi-character ops and user-defined operators */ %left '+' '-' @@ -850,6 +877,10 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); /* kluge to keep xml_whitespace_option from causing shift/reduce conflicts */ %right PRESERVE STRIP_P +%nonassoc json_table_column +%nonassoc NESTED +%left PATH + %nonassoc empty_json_unique %left WITHOUT WITH_LA_UNIQUE @@ -11938,6 +11969,19 @@ table_ref: relation_expr opt_alias_clause $2->alias = $4; $$ = (Node *) $2; } + | json_table opt_alias_clause + { + JsonTable *jt = castNode(JsonTable, $1); + jt->alias = $2; + $$ = (Node *) jt; + } + | LATERAL_P json_table opt_alias_clause + { + JsonTable *jt = castNode(JsonTable, $2); + jt->alias = $3; + jt->lateral = true; + $$ = (Node *) jt; + } ; @@ -12441,6 +12485,8 @@ xmltable_column_option_el: { $$ = makeDefElem("is_not_null", (Node *) makeInteger(true), @1); } | NULL_P { $$ = makeDefElem("is_not_null", (Node *) makeInteger(false), @1); } + | PATH b_expr + { $$ = makeDefElem("path", $2, @1); } ; xml_namespace_list: @@ -14856,6 +14902,10 @@ json_behavior_unknown: UNKNOWN { $$ = makeJsonBehavior(JSON_BEHAVIOR_UNKNOWN, NULL); } ; +json_behavior_empty: + EMPTY_P { $$ = makeJsonBehavior(JSON_BEHAVIOR_EMPTY_OBJECT, NULL); } + ; + json_behavior_empty_array: EMPTY_P ARRAY { $$ = makeJsonBehavior(JSON_BEHAVIOR_EMPTY_ARRAY, NULL); } ; @@ -14967,6 +15017,235 @@ json_query_on_behavior_clause_opt: { $$.on_empty = NULL; $$.on_error = NULL; } ; +json_table: + JSON_TABLE '(' + json_api_common_syntax + json_table_columns_clause + json_table_plan_clause_opt + json_table_error_clause_opt + ')' + { + JsonTable *n = makeNode(JsonTable); + n->common = (JsonCommon *) $3; + n->columns = $4; + n->plan = (JsonTablePlan *) $5; + n->on_error = $6; + n->location = @1; + $$ = (Node *) n; + } + ; + +json_table_columns_clause: + COLUMNS '(' json_table_column_definition_list ')' { $$ = $3; } + ; + +json_table_column_definition_list: + json_table_column_definition + { $$ = list_make1($1); } + | json_table_column_definition_list ',' json_table_column_definition + { $$ = lappend($1, $3); } + ; + +json_table_column_definition: + json_table_ordinality_column_definition %prec json_table_column + | json_table_regular_column_definition %prec json_table_column + | json_table_formatted_column_definition %prec json_table_column + | json_table_nested_columns + ; + +json_table_ordinality_column_definition: + ColId FOR ORDINALITY + { + JsonTableColumn *n = makeNode(JsonTableColumn); + n->coltype = JTC_FOR_ORDINALITY; + n->name = $1; + n->location = @1; + $$ = (Node *) n; + } + ; + +json_table_regular_column_definition: + ColId Typename + json_table_column_path_specification_clause_opt + json_value_on_behavior_clause_opt + { + JsonTableColumn *n = makeNode(JsonTableColumn); + n->coltype = JTC_REGULAR; + n->name = $1; + n->typename = $2; + n->format.type = JS_FORMAT_DEFAULT; + n->format.encoding = JS_ENC_DEFAULT; + n->wrapper = JSW_NONE; + n->omit_quotes = false; + n->pathspec = $3; + n->on_empty = $4.on_empty; + n->on_error = $4.on_error; + n->location = @1; + $$ = (Node *) n; + } + ; + +json_table_error_behavior: + json_behavior_error + | json_behavior_empty + ; + +json_table_error_clause_opt: + json_table_error_behavior ON ERROR_P { $$ = $1; } + | /* EMPTY */ { $$ = NULL; } + ; + +json_table_column_path_specification_clause_opt: + PATH Sconst { $$ = $2; } + | /* EMPTY */ %prec json_table_column { $$ = NULL; } + ; + +json_table_formatted_column_definition: + ColId Typename FORMAT json_representation + json_table_column_path_specification_clause_opt + json_wrapper_clause_opt + json_quotes_clause_opt + json_query_on_behavior_clause_opt + { + JsonTableColumn *n = makeNode(JsonTableColumn); + n->coltype = JTC_FORMATTED; + n->name = $1; + n->typename = $2; + n->format = $4; + n->pathspec = $5; + n->wrapper = $6; + if (n->wrapper != JSW_NONE && $7 != JS_QUOTES_UNSPEC) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("SQL/JSON QUOTES behavior shall not be specified when WITH WRAPPER is used"), + parser_errposition(@7))); + n->omit_quotes = $7 == JS_QUOTES_OMIT; + n->on_empty = $8.on_empty; + n->on_error = $8.on_error; + n->location = @1; + $$ = (Node *) n; + } + ; + +json_table_nested_columns: + NESTED path_opt Sconst + json_as_path_name_clause_opt + json_table_columns_clause + { + JsonTableColumn *n = makeNode(JsonTableColumn); + n->coltype = JTC_NESTED; + n->pathspec = $3; + n->pathname = $4; + n->columns = $5; + n->location = @1; + $$ = (Node *) n; + } + ; + +path_opt: + PATH { } + | /* EMPTY */ { } + ; + +json_table_plan_clause_opt: + json_table_specific_plan { $$ = $1; } + | json_table_default_plan { $$ = $1; } + | /* EMPTY */ { $$ = NULL; } + ; + +json_table_specific_plan: + PLAN '(' json_table_plan ')' { $$ = $3; } + ; + +json_table_plan: + json_table_plan_simple + | json_table_plan_parent_child + | json_table_plan_sibling + ; + +json_table_plan_simple: + json_table_path_name + { + JsonTablePlan *n = makeNode(JsonTablePlan); + n->plan_type = JSTP_SIMPLE; + n->pathname = $1; + n->location = @1; + $$ = (Node *) n; + } + ; + +json_table_plan_parent_child: + json_table_plan_outer + | json_table_plan_inner + ; + +json_table_plan_outer: + json_table_plan_simple OUTER_P json_table_plan_primary + { $$ = makeJsonTableJoinedPlan(JSTP_OUTER, $1, $3, @1); } + ; + +json_table_plan_inner: + json_table_plan_simple INNER_P json_table_plan_primary + { $$ = makeJsonTableJoinedPlan(JSTP_INNER, $1, $3, @1); } + ; + +json_table_plan_sibling: + json_table_plan_union + | json_table_plan_cross + ; + +json_table_plan_union: + json_table_plan_primary UNION json_table_plan_primary + { $$ = makeJsonTableJoinedPlan(JSTP_UNION, $1, $3, @1); } + | json_table_plan_union UNION json_table_plan_primary + { $$ = makeJsonTableJoinedPlan(JSTP_UNION, $1, $3, @1); } + ; + +json_table_plan_cross: + json_table_plan_primary CROSS json_table_plan_primary + { $$ = makeJsonTableJoinedPlan(JSTP_CROSS, $1, $3, @1); } + | json_table_plan_cross CROSS json_table_plan_primary + { $$ = makeJsonTableJoinedPlan(JSTP_CROSS, $1, $3, @1); } + ; + +json_table_plan_primary: + json_table_plan_simple { $$ = $1; } + | '(' json_table_plan ')' + { + castNode(JsonTablePlan, $2)->location = @1; + $$ = $2; + } + ; + +json_table_default_plan: + PLAN DEFAULT '(' json_table_default_plan_choices ')' + { + JsonTablePlan *n = makeNode(JsonTablePlan); + n->plan_type = JSTP_DEFAULT; + n->join_type = $4; + n->location = @1; + $$ = (Node *) n; + } + ; + +json_table_default_plan_choices: + json_table_default_plan_inner_outer { $$ = $1 | JSTP_UNION; } + | json_table_default_plan_inner_outer ',' + json_table_default_plan_union_cross { $$ = $1 | $3; } + | json_table_default_plan_union_cross { $$ = $1 | JSTP_OUTER; } + | json_table_default_plan_union_cross ',' + json_table_default_plan_inner_outer { $$ = $1 | $3; } + ; + +json_table_default_plan_inner_outer: + INNER_P { $$ = JSTP_INNER; } + | OUTER_P { $$ = JSTP_OUTER; } + ; + +json_table_default_plan_union_cross: + UNION { $$ = JSTP_UNION; } + | CROSS { $$ = JSTP_CROSS; } + ; json_output_clause_opt: RETURNING Typename json_format_clause_opt @@ -15732,6 +16011,7 @@ unreserved_keyword: | MOVE | NAME_P | NAMES + | NESTED | NEW | NEXT | NO @@ -15760,6 +16040,8 @@ unreserved_keyword: | PARTITION | PASSING | PASSWORD + | PATH + | PLAN | PLANS | POLICY | PRECEDING @@ -15916,6 +16198,7 @@ col_name_keyword: | JSON_OBJECT | JSON_OBJECTAGG | JSON_QUERY + | JSON_TABLE | JSON_VALUE | LEAST | NATIONAL diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c index 3a02307..54ce2ae 100644 --- a/src/backend/parser/parse_clause.c +++ b/src/backend/parser/parse_clause.c @@ -49,10 +49,20 @@ #include "utils/builtins.h" #include "utils/guc.h" #include "utils/catcache.h" +#include "utils/json.h" #include "utils/lsyscache.h" #include "utils/syscache.h" #include "utils/rel.h" +/* Context for JSON_TABLE transformation */ +typedef struct JsonTableContext +{ + JsonTable *table; /* untransformed node */ + TableFunc *tablefunc; /* transformed node */ + List *pathNames; /* list of all path and columns names */ + int pathNameId; /* path name id counter */ + Oid contextItemTypid; /* type oid of context item (json/jsonb) */ +} JsonTableContext; /* Convenience macro for the most common makeNamespaceItem() case */ #define makeDefaultNSItem(rte) makeNamespaceItem(rte, true, true, false, true) @@ -103,6 +113,10 @@ static WindowClause *findWindowClause(List *wclist, const char *name); static Node *transformFrameOffset(ParseState *pstate, int frameOptions, Oid rangeopfamily, Oid rangeopcintype, Oid *inRangeFunc, Node *clause); +static JsonTableParentNode * transformJsonTableColumns(ParseState *pstate, + JsonTableContext *cxt, JsonTablePlan *plan, + List *columns, char *pathSpec, char **pathName, + int location); /* @@ -760,6 +774,8 @@ transformRangeTableFunc(ParseState *pstate, RangeTableFunc *rtf) Assert(!pstate->p_lateral_active); pstate->p_lateral_active = true; + tf->functype = TFT_XMLTABLE; + /* Transform and apply typecast to the row-generating expression ... */ Assert(rtf->rowexpr != NULL); tf->rowexpr = coerce_to_specific_type(pstate, @@ -1049,6 +1065,18 @@ transformRangeTableSample(ParseState *pstate, RangeTableSample *rts) return tablesample; } +static Node * +makeStringConst(char *str, int location) +{ + A_Const *n = makeNode(A_Const); + + n->val.type = T_String; + n->val.val.str = str; + n->location = location; + + return (Node *)n; +} + /* * getRTEForSpecialRelationTypes * @@ -1080,6 +1108,633 @@ getRTEForSpecialRelationTypes(ParseState *pstate, RangeVar *rv) } /* + * Transform JSON_TABLE column + * - regular column into JSON_VALUE() + * - formatted column into JSON_QUERY() + */ +static Node * +transformJsonTableColumn(JsonTableColumn *jtc, Node *contextItemExpr, + List *passingArgs, bool errorOnError) +{ + JsonFuncExpr *jfexpr = makeNode(JsonFuncExpr); + JsonValueExpr *jvexpr = makeNode(JsonValueExpr); + JsonCommon *common = makeNode(JsonCommon); + JsonOutput *output = makeNode(JsonOutput); + JsonPathSpec pathspec; + + jfexpr->op = jtc->coltype == JTC_REGULAR ? IS_JSON_VALUE : IS_JSON_QUERY; + jfexpr->common = common; + jfexpr->output = output; + jfexpr->on_empty = jtc->on_empty; + jfexpr->on_error = jtc->on_error; + if (!jfexpr->on_error && errorOnError) + jfexpr->on_error = makeJsonBehavior(JSON_BEHAVIOR_ERROR, NULL); + jfexpr->omit_quotes = jtc->omit_quotes; + jfexpr->wrapper = jtc->wrapper; + jfexpr->location = jtc->location; + + output->typename = jtc->typename; + output->returning.format = jtc->format; + + common->pathname = NULL; + common->expr = jvexpr; + common->passing = passingArgs; + + if (jtc->pathspec) + pathspec = jtc->pathspec; + else + { + /* Construct default path as '$."column_name"' */ + StringInfoData path; + + initStringInfo(&path); + + appendStringInfoString(&path, "$."); + escape_json(&path, jtc->name); + + pathspec = path.data; + } + + common->pathspec = makeStringConst(pathspec, -1); + + jvexpr->expr = (Expr *) contextItemExpr; + jvexpr->format.type = JS_FORMAT_DEFAULT; + jvexpr->format.encoding = JS_ENC_DEFAULT; + + return (Node *) jfexpr; +} + +static bool +isJsonTablePathNameDuplicate(JsonTableContext *cxt, const char *pathname) +{ + ListCell *lc; + + foreach(lc, cxt->pathNames) + { + if (!strcmp(pathname, (const char *) lfirst(lc))) + return true; + } + + return false; +} + +/* Recursively register column name in the path name list. */ +static void +registerJsonTableColumn(JsonTableContext *cxt, char *colname) +{ + if (isJsonTablePathNameDuplicate(cxt, colname)) + ereport(ERROR, + (errcode(ERRCODE_DUPLICATE_ALIAS), + errmsg("duplicate JSON_TABLE column name: %s", colname), + errhint("JSON_TABLE path names and column names shall be " + "distinct from one another"))); + + cxt->pathNames = lappend(cxt->pathNames, colname); +} + +/* Recursively register all nested column names in the path name list. */ +static void +registerAllJsonTableColumns(JsonTableContext *cxt, List *columns) +{ + ListCell *lc; + + foreach(lc, columns) + { + JsonTableColumn *jtc = castNode(JsonTableColumn, lfirst(lc)); + + if (jtc->coltype == JTC_NESTED) + { + if (jtc->pathname) + registerJsonTableColumn(cxt, jtc->pathname); + + registerAllJsonTableColumns(cxt, jtc->columns); + } + else + { + registerJsonTableColumn(cxt, jtc->name); + } + } +} + +/* Generate a new unique JSON_TABLE path name. */ +static char * +generateJsonTablePathName(JsonTableContext *cxt) +{ + char namebuf[32]; + char *name = namebuf; + + do + { + snprintf(namebuf, sizeof(namebuf), "json_table_path_%d", + ++cxt->pathNameId); + } while (isJsonTablePathNameDuplicate(cxt, name)); + + name = pstrdup(name); + cxt->pathNames = lappend(cxt->pathNames, name); + + return name; +} + +/* Collect sibling path names from plan to the specified list. */ +static void +collectSiblingPathsInJsonTablePlan(JsonTablePlan *plan, List **paths) +{ + if (plan->plan_type == JSTP_SIMPLE) + *paths = lappend(*paths, plan->pathname); + else if (plan->plan_type == JSTP_JOINED) + { + if (plan->join_type == JSTP_INNER || + plan->join_type == JSTP_OUTER) + { + Assert(plan->plan1->plan_type == JSTP_SIMPLE); + *paths = lappend(*paths, plan->plan1->pathname); + } + else if (plan->join_type == JSTP_CROSS || + plan->join_type == JSTP_UNION) + { + collectSiblingPathsInJsonTablePlan(plan->plan1, paths); + collectSiblingPathsInJsonTablePlan(plan->plan2, paths); + } + else + elog(ERROR, "invalid JSON_TABLE join type %d", + plan->join_type); + } +} + +/* + * Validate child JSON_TABLE plan by checking that: + * - all nested columns have path names specified + * - all nested columns have corresponding node in the sibling plan + * - plan does not contain duplicate or extra nodes + */ +static void +validateJsonTableChildPlan(ParseState *pstate, JsonTablePlan *plan, + List *columns) +{ + ListCell *lc1; + List *siblings = NIL; + int nchilds = 0; + + if (plan) + collectSiblingPathsInJsonTablePlan(plan, &siblings); + + foreach(lc1, columns) + { + JsonTableColumn *jtc = castNode(JsonTableColumn, lfirst(lc1)); + + if (jtc->coltype == JTC_NESTED) + { + ListCell *lc2; + bool found = false; + + if (!jtc->pathname) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("nested JSON_TABLE columns shall contain " + "explicit AS pathname specification if " + "explicit PLAN clause is used"), + parser_errposition(pstate, jtc->location))); + + /* find nested path name in the list of sibling path names */ + foreach(lc2, siblings) + { + if ((found = !strcmp(jtc->pathname, lfirst(lc2)))) + break; + } + + if (!found) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("invalid JSON_TABLE plan"), + errdetail("plan node for nested path %s " + "was not found in plan", jtc->pathname), + parser_errposition(pstate, jtc->location))); + + nchilds++; + } + } + + if (list_length(siblings) > nchilds) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("invalid JSON_TABLE plan"), + errdetail("plan node contains some extra or " + "duplicate sibling nodes"), + parser_errposition(pstate, plan ? plan->location : -1))); +} + +static JsonTableColumn * +findNestedJsonTableColumn(List *columns, const char *pathname) +{ + ListCell *lc; + + foreach(lc, columns) + { + JsonTableColumn *jtc = castNode(JsonTableColumn, lfirst(lc)); + + if (jtc->coltype == JTC_NESTED && + jtc->pathname && + !strcmp(jtc->pathname, pathname)) + return jtc; + } + + return NULL; +} + +static Node * +transformNestedJsonTableColumn(ParseState *pstate, JsonTableContext *cxt, + JsonTableColumn *jtc, JsonTablePlan *plan) +{ + JsonTableParentNode *node; + char *pathname = jtc->pathname; + + node = transformJsonTableColumns(pstate, cxt, plan, + jtc->columns, jtc->pathspec, + &pathname, jtc->location); + node->name = pstrdup(pathname); + + return (Node *) node; +} + +static Node * +makeJsonTableSiblingJoin(bool cross, Node *lnode, Node *rnode) +{ + JsonTableSiblingNode *join = makeNode(JsonTableSiblingNode); + + join->larg = lnode; + join->rarg = rnode; + join->cross = cross; + + return (Node *) join; +} + +/* + * Recursively transform child JSON_TABLE plan. + * + * Default plan is transformed into a cross/union join of its nested columns. + * Simple and outer/inner plans are transformed into a JsonTableParentNode by + * finding and transforming corresponding nested column. + * Sibling plans are recursively transformed into a JsonTableSiblingNode. + */ +static Node * +transformJsonTableChildPlan(ParseState *pstate, JsonTableContext *cxt, + JsonTablePlan *plan, List *columns) +{ + JsonTableColumn *jtc = NULL; + + if (!plan || plan->plan_type == JSTP_DEFAULT) + { + /* unspecified or default plan */ + Node *res = NULL; + ListCell *lc; + bool cross = plan && (plan->join_type & JSTP_CROSS); + + /* transform all nested columns into cross/union join */ + foreach(lc, columns) + { + JsonTableColumn *jtc = castNode(JsonTableColumn, lfirst(lc)); + Node *node; + + if (jtc->coltype != JTC_NESTED) + continue; + + node = transformNestedJsonTableColumn(pstate, cxt, jtc, plan); + + /* join transformed node with previous sibling nodes */ + res = res ? makeJsonTableSiblingJoin(cross, res, node) : node; + } + + return res; + } + else if (plan->plan_type == JSTP_SIMPLE) + { + jtc = findNestedJsonTableColumn(columns, plan->pathname); + } + else if (plan->plan_type == JSTP_JOINED) + { + if (plan->join_type == JSTP_INNER || + plan->join_type == JSTP_OUTER) + { + Assert(plan->plan1->plan_type == JSTP_SIMPLE); + jtc = findNestedJsonTableColumn(columns, plan->plan1->pathname); + } + else + { + Node *node1 = + transformJsonTableChildPlan(pstate, cxt, plan->plan1, columns); + Node *node2 = + transformJsonTableChildPlan(pstate, cxt, plan->plan2, columns); + + return makeJsonTableSiblingJoin(plan->join_type == JSTP_CROSS, + node1, node2); + } + } + else + elog(ERROR, "invalid JSON_TABLE plan type %d", plan->plan_type); + + if (!jtc) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("invalid JSON_TABLE plan"), + errdetail("path name was %s not found in nested columns list", + plan->pathname), + parser_errposition(pstate, plan->location))); + + return transformNestedJsonTableColumn(pstate, cxt, jtc, plan); +} + +/* Append transformed non-nested JSON_TABLE columns to the TableFunc node */ +static void +appendJsonTableColumns(ParseState *pstate, JsonTableContext *cxt, List *columns) +{ + JsonTable *jt = cxt->table; + TableFunc *tf = cxt->tablefunc; + bool errorOnError = jt->on_error && + jt->on_error->btype == JSON_BEHAVIOR_ERROR; + ListCell *col; + + foreach(col, columns) + { + JsonTableColumn *rawc = castNode(JsonTableColumn, lfirst(col)); + Oid typid; + int32 typmod; + Node *colexpr; + + if (rawc->name) + { + /* make sure column names are unique */ + ListCell *colname; + + foreach(colname, tf->colnames) + if (!strcmp((const char *) colname, rawc->name)) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("column name \"%s\" is not unique", + rawc->name), + parser_errposition(pstate, rawc->location))); + + tf->colnames = lappend(tf->colnames, + makeString(pstrdup(rawc->name))); + } + + /* + * Determine the type and typmod for the new column. FOR + * ORDINALITY columns are INTEGER by standard; the others are + * user-specified. + */ + switch (rawc->coltype) + { + case JTC_FOR_ORDINALITY: + colexpr = NULL; + typid = INT4OID; + typmod = -1; + break; + + case JTC_REGULAR: + case JTC_FORMATTED: + { + Node *je; + CaseTestExpr *param = makeNode(CaseTestExpr); + + param->collation = InvalidOid; + param->typeId = cxt->contextItemTypid; + param->typeMod = -1; + + je = transformJsonTableColumn(rawc, (Node *) param, + NIL, errorOnError); + + colexpr = transformExpr(pstate, je, EXPR_KIND_FROM_FUNCTION); + assign_expr_collations(pstate, colexpr); + + typid = exprType(colexpr); + typmod = exprTypmod(colexpr); + break; + } + + case JTC_NESTED: + continue; + + default: + elog(ERROR, "unknown JSON_TABLE column type: %d", rawc->coltype); + break; + } + + tf->coltypes = lappend_oid(tf->coltypes, typid); + tf->coltypmods = lappend_int(tf->coltypmods, typmod); + tf->colcollations = lappend_oid(tf->colcollations, + type_is_collatable(typid) + ? DEFAULT_COLLATION_OID + : InvalidOid); + tf->colvalexprs = lappend(tf->colvalexprs, colexpr); + } +} + +/* + * Create transformed JSON_TABLE parent plan node by appending all non-nested + * columns to the TableFunc node and remembering their indices in the + * colvalexprs list. + */ +static JsonTableParentNode * +makeParentJsonTableNode(ParseState *pstate, JsonTableContext *cxt, + char *pathSpec, List *columns) +{ + JsonTableParentNode *node = makeNode(JsonTableParentNode); + + node->path = makeConst(JSONPATHOID, -1, InvalidOid, -1, + DirectFunctionCall1(jsonpath_in, + CStringGetDatum(pathSpec)), + false, false); + + /* save start of column range */ + node->colMin = list_length(cxt->tablefunc->colvalexprs); + + appendJsonTableColumns(pstate, cxt, columns); + + /* save end of column range */ + node->colMax = list_length(cxt->tablefunc->colvalexprs) - 1; + + node->errorOnError = + cxt->table->on_error && + cxt->table->on_error->btype == JSON_BEHAVIOR_ERROR; + + return node; +} + +static JsonTableParentNode * +transformJsonTableColumns(ParseState *pstate, JsonTableContext *cxt, + JsonTablePlan *plan, List *columns, + char *pathSpec, char **pathName, int location) +{ + JsonTableParentNode *node; + JsonTablePlan *childPlan; + bool defaultPlan = !plan || plan->plan_type == JSTP_DEFAULT; + + if (!*pathName) + { + if (cxt->table->plan) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("invalid JSON_TABLE expression"), + errdetail("JSON_TABLE columns shall contain " + "explicit AS pathname specification if " + "explicit PLAN clause is used"), + parser_errposition(pstate, location))); + + *pathName = generateJsonTablePathName(cxt); + } + + if (defaultPlan) + childPlan = plan; + else + { + /* validate parent and child plans */ + JsonTablePlan *parentPlan = + plan->plan_type == JSTP_JOINED ? plan->plan1 : plan; + + if (strcmp(parentPlan->pathname, *pathName)) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("invalid JSON_TABLE plan"), + errdetail("path name mismatch: expected %s but %s is given", + *pathName, parentPlan->pathname), + parser_errposition(pstate, plan->location))); + + if (plan->plan_type == JSTP_JOINED) + { + if (plan->join_type != JSTP_INNER && + plan->join_type != JSTP_OUTER) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("invalid JSON_TABLE plan"), + errdetail("expected INNER or OUTER JSON_TABLE plan node"), + parser_errposition(pstate, plan->location))); + + childPlan = plan->plan2; + } + else + childPlan = NULL; + + validateJsonTableChildPlan(pstate, childPlan, columns); + } + + /* transform only non-nested columns */ + node = makeParentJsonTableNode(pstate, cxt, pathSpec, columns); + node->name = pstrdup(*pathName); + + if (childPlan || defaultPlan) + { + /* transform recursively nested columns */ + node->child = transformJsonTableChildPlan(pstate, cxt, childPlan, + columns); + if (node->child) + node->outerJoin = !plan || (plan->join_type & JSTP_OUTER); + /* else: default plan case, no children found */ + } + + return node; +} + +/* + * transformJsonTable - + * Transform a raw JsonTable into TableFunc. + * + * Transform the document-generating expression, the row-generating expression, + * the column-generating expressions, and the default value expressions. + */ +static RangeTblEntry * +transformJsonTable(ParseState *pstate, JsonTable *jt) +{ + JsonTableContext cxt; + TableFunc *tf = makeNode(TableFunc); + JsonFuncExpr *jfe = makeNode(JsonFuncExpr); + JsonCommon *jscommon; + JsonTablePlan *plan = jt->plan; + char *rootPathName = jt->common->pathname; + char *rootPath; + bool is_lateral; + + cxt.table = jt; + cxt.tablefunc = tf; + cxt.pathNames = NIL; + cxt.pathNameId = 0; + + if (rootPathName) + registerJsonTableColumn(&cxt, rootPathName); + + registerAllJsonTableColumns(&cxt, jt->columns); + + if (plan && plan->plan_type != JSTP_DEFAULT && !rootPathName) + { + /* Assign root path name and create corresponding plan node */ + JsonTablePlan *rootNode = makeNode(JsonTablePlan); + JsonTablePlan *rootPlan = (JsonTablePlan *) + makeJsonTableJoinedPlan(JSTP_OUTER, (Node *) rootNode, + (Node *) plan, jt->location); + + rootPathName = generateJsonTablePathName(&cxt); + + rootNode->plan_type = JSTP_SIMPLE; + rootNode->pathname = rootPathName; + + plan = rootPlan; + } + + jscommon = copyObject(jt->common); + jscommon->pathspec = makeStringConst(pstrdup("$"), -1); + + jfe->op = IS_JSON_TABLE; + jfe->common = jscommon; + jfe->on_error = jt->on_error; + jfe->location = jt->common->location; + + /* + * We make lateral_only names of this level visible, whether or not the + * RangeTableFunc is explicitly marked LATERAL. This is needed for SQL + * spec compliance and seems useful on convenience grounds for all + * functions in FROM. + * + * (LATERAL can't nest within a single pstate level, so we don't need + * save/restore logic here.) + */ + Assert(!pstate->p_lateral_active); + pstate->p_lateral_active = true; + + tf->functype = TFT_JSON_TABLE; + tf->docexpr = transformExpr(pstate, (Node *) jfe, EXPR_KIND_FROM_FUNCTION); + + cxt.contextItemTypid = exprType(tf->docexpr); + + if (!IsA(jt->common->pathspec, A_Const) || + castNode(A_Const, jt->common->pathspec)->val.type != T_String) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("only string constants supported in JSON_TABLE path specification"), + parser_errposition(pstate, + exprLocation(jt->common->pathspec)))); + + rootPath = castNode(A_Const, jt->common->pathspec)->val.val.str; + + tf->plan = (Node *) transformJsonTableColumns(pstate, &cxt, plan, + jt->columns, + rootPath, &rootPathName, + jt->common->location); + + tf->ordinalitycol = -1; /* undefine ordinality column number */ + tf->location = jt->location; + + pstate->p_lateral_active = false; + + /* + * Mark the RTE as LATERAL if the user said LATERAL explicitly, or if + * there are any lateral cross-references in it. + */ + is_lateral = jt->lateral || contain_vars_of_level((Node *) tf, 0); + + return addRangeTableEntryForTableFunc(pstate, + tf, jt->alias, is_lateral, true); +} + +/* * transformFromClauseItem - * Transform a FROM-clause item, adding any required entries to the * range table list being built in the ParseState, and return the @@ -1211,6 +1866,31 @@ transformFromClauseItem(ParseState *pstate, Node *n, rte->tablesample = transformRangeTableSample(pstate, rts); return (Node *) rtr; } + else if (IsA(n, JsonTable)) + { + /* JsonTable is transformed into RangeSubselect */ + /* + JsonTable *jt = castNode(JsonTable, n); + RangeSubselect *subselect = transformJsonTable(pstate, jt); + + return transformFromClauseItem(pstate, (Node *) subselect, + top_rte, top_rti, namespace); + */ + RangeTblRef *rtr; + RangeTblEntry *rte; + int rtindex; + + rte = transformJsonTable(pstate, (JsonTable *) n); + /* assume new rte is at end */ + rtindex = list_length(pstate->p_rtable); + Assert(rte == rt_fetch(rtindex, pstate->p_rtable)); + *top_rte = rte; + *top_rti = rtindex; + *namespace = list_make1(makeDefaultNSItem(rte)); + rtr = makeNode(RangeTblRef); + rtr->rtindex = rtindex; + return (Node *) rtr; + } else if (IsA(n, JoinExpr)) { /* A newfangled join expression */ diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index 2ea7cff..7d19881 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -4411,7 +4411,7 @@ transformJsonExprCommon(ParseState *pstate, JsonFuncExpr *func) Node *pathspec; JsonFormatType format; - if (func->common->pathname) + if (func->common->pathname && func->op != IS_JSON_TABLE) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("JSON_TABLE path name is not allowed here"), @@ -4457,14 +4457,13 @@ transformJsonExprCommon(ParseState *pstate, JsonFuncExpr *func) transformJsonPassingArgs(pstate, format, func->common->passing, &jsexpr->passing); - if (func->op != IS_JSON_EXISTS) + if (func->op != IS_JSON_EXISTS && func->op != IS_JSON_TABLE) jsexpr->on_empty = transformJsonBehavior(pstate, func->on_empty, JSON_BEHAVIOR_NULL); jsexpr->on_error = transformJsonBehavior(pstate, func->on_error, - func->op == IS_JSON_EXISTS ? - JSON_BEHAVIOR_FALSE : - JSON_BEHAVIOR_NULL); + func->op == IS_JSON_EXISTS ? JSON_BEHAVIOR_FALSE : + func->op == IS_JSON_TABLE ? JSON_BEHAVIOR_EMPTY : JSON_BEHAVIOR_NULL); return jsexpr; } @@ -4718,6 +4717,21 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func) jsexpr->returning.typmod = -1; break; + + case IS_JSON_TABLE: + jsexpr->returning.format.type = JS_FORMAT_DEFAULT; + jsexpr->returning.format.encoding = JS_ENC_DEFAULT; + jsexpr->returning.format.location = -1; + jsexpr->returning.typid = exprType(contextItemExpr); + jsexpr->returning.typmod = -1; + + if (jsexpr->returning.typid != JSONBOID) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("JSON_TABLE() is not yet implemented for json type"), + parser_errposition(pstate, func->location))); + + break; } return (Node *) jsexpr; diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c index 053ae02..8ccb34a 100644 --- a/src/backend/parser/parse_relation.c +++ b/src/backend/parser/parse_relation.c @@ -1662,7 +1662,8 @@ addRangeTableEntryForTableFunc(ParseState *pstate, bool inFromCl) { RangeTblEntry *rte = makeNode(RangeTblEntry); - char *refname = alias ? alias->aliasname : pstrdup("xmltable"); + char *refname = alias ? alias->aliasname : + pstrdup(tf->functype == TFT_XMLTABLE ? "xmltable" : "json_table"); Alias *eref; int numaliases; diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c index e5a71c5..74bb958 100644 --- a/src/backend/parser/parse_target.c +++ b/src/backend/parser/parse_target.c @@ -1943,6 +1943,9 @@ FigureColnameInternal(Node *node, char **name) case IS_JSON_EXISTS: *name = "json_exists"; return 2; + case IS_JSON_TABLE: + *name = "json_table"; + return 2; } break; default: diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c index dd36829..7f68d31 100644 --- a/src/backend/utils/adt/jsonpath_exec.c +++ b/src/backend/utils/adt/jsonpath_exec.c @@ -15,12 +15,16 @@ #include "miscadmin.h" #include "catalog/pg_collation.h" #include "catalog/pg_type.h" +#include "executor/execExpr.h" #include "lib/stringinfo.h" +#include "nodes/nodeFuncs.h" #include "regex/regex.h" #include "utils/builtins.h" #include "utils/formatting.h" #include "utils/json.h" #include "utils/jsonpath.h" +#include "utils/lsyscache.h" +#include "utils/memutils.h" #include "utils/varlena.h" #ifdef JSONPATH_JSON_C @@ -44,6 +48,57 @@ typedef struct JsonValueListIterator #define JsonValueListIteratorEnd ((ListCell *) -1) +typedef struct JsonTableScanState JsonTableScanState; +typedef struct JsonTableJoinState JsonTableJoinState; + +struct JsonTableScanState +{ + JsonTableScanState *parent; + JsonTableJoinState *nested; + MemoryContext mcxt; + JsonPath *path; + List *args; + JsonValueList found; + JsonValueListIterator iter; + Datum current; + int ordinal; + bool outerJoin; + bool errorOnError; + bool advanceNested; + bool reset; +}; + +struct JsonTableJoinState +{ + union + { + struct + { + JsonTableJoinState *left; + JsonTableJoinState *right; + bool cross; + bool advanceRight; + } join; + JsonTableScanState scan; + } u; + bool is_join; +}; + +/* random number to identify JsonTableContext */ +#define JSON_TABLE_CONTEXT_MAGIC 418352867 + +typedef struct JsonTableContext +{ + int magic; + struct + { + ExprState *expr; + JsonTableScanState *scan; + } *colexprs; + JsonTableScanState root; + bool empty; +} JsonTableContext; + static inline JsonPathExecResult recursiveExecute(JsonPathExecContext *cxt, JsonPathItem *jsp, JsonbValue *jb, JsonValueList *found); @@ -57,6 +112,12 @@ static inline JsonPathExecResult recursiveExecuteUnwrap(JsonPathExecContext *cxt static inline JsonbValue *wrapItemsInArray(const JsonValueList *items); +static JsonTableJoinState *JsonTableInitPlanState(JsonTableContext *cxt, + Node *plan, JsonTableScanState *parent); + +static bool JsonTableNextRow(JsonTableScanState *scan); + + static inline void JsonValueListAppend(JsonValueList *jvl, JsonbValue *jbv) { @@ -2800,3 +2861,419 @@ JsonbPathValue(Datum jb, JsonPath *jp, bool *empty, List *vars) return res; } + +/* + * Returns private data from executor state. Ensure validity by check with + * MAGIC number. + */ +static inline JsonTableContext * +GetJsonTableContext(TableFuncScanState *state, const char *fname) +{ + JsonTableContext *result; + + if (!IsA(state, TableFuncScanState)) + elog(ERROR, "%s called with invalid TableFuncScanState", fname); + result = (JsonTableContext *) state->opaque; + if (result->magic != JSON_TABLE_CONTEXT_MAGIC) + elog(ERROR, "%s called with invalid TableFuncScanState", fname); + + return result; +} + +/* Recursively initialize JSON_TABLE scan state */ +static void +JsonTableInitScanState(JsonTableContext *cxt, JsonTableScanState *scan, + JsonTableParentNode *node, JsonTableScanState *parent, + List *args, MemoryContext mcxt) +{ + int i; + + scan->parent = parent; + scan->outerJoin = node->outerJoin; + scan->errorOnError = node->errorOnError; + scan->path = DatumGetJsonPathP(node->path->constvalue); + scan->args = args; + scan->mcxt = AllocSetContextCreate(mcxt, "JsonTableContext", + ALLOCSET_DEFAULT_SIZES); + scan->nested = node->child ? + JsonTableInitPlanState(cxt, node->child, scan) : NULL; + + for (i = node->colMin; i <= node->colMax; i++) + cxt->colexprs[i].scan = scan; +} + +/* Recursively initialize JSON_TABLE scan state */ +static JsonTableJoinState * +JsonTableInitPlanState(JsonTableContext *cxt, Node *plan, + JsonTableScanState *parent) +{ + JsonTableJoinState *state = palloc0(sizeof(*state)); + + if (IsA(plan, JsonTableSiblingNode)) + { + JsonTableSiblingNode *join = castNode(JsonTableSiblingNode, plan); + + state->is_join = true; + state->u.join.cross = join->cross; + state->u.join.left = JsonTableInitPlanState(cxt, join->larg, parent); + state->u.join.right = JsonTableInitPlanState(cxt, join->rarg, parent); + } + else + { + JsonTableParentNode *node = castNode(JsonTableParentNode, plan); + + state->is_join = false; + + JsonTableInitScanState(cxt, &state->u.scan, node, parent, + parent->args, parent->mcxt); + } + + return state; +} + +/* + * JsonTableInitOpaque + * Fill in TableFuncScanState->opaque for JsonTable processor + */ +static void +JsonTableInitOpaque(TableFuncScanState *state, int natts) +{ + JsonTableContext *cxt; + PlanState *ps = &state->ss.ps; + TableFuncScan *tfs = castNode(TableFuncScan, ps->plan); + TableFunc *tf = tfs->tablefunc; + JsonExpr *ci = castNode(JsonExpr, tf->docexpr); + JsonTableParentNode *root = castNode(JsonTableParentNode, tf->plan); + List *args = NIL; + ListCell *lc; + int i; + + cxt = palloc0(sizeof(JsonTableContext)); + cxt->magic = JSON_TABLE_CONTEXT_MAGIC; + + if (list_length(ci->passing.values) > 0) + { + ListCell *exprlc; + ListCell *namelc; + + forboth(exprlc, ci->passing.values, + namelc, ci->passing.names) + { + Expr *expr = (Expr *) lfirst(exprlc); + Value *name = (Value *) lfirst(namelc); + JsonPathVariableEvalContext *var = palloc(sizeof(*var)); + + var->var.varName = cstring_to_text(name->val.str); + var->var.typid = exprType((Node *) expr); + var->var.typmod = exprTypmod((Node *) expr); + var->var.cb = EvalJsonPathVar; + var->var.cb_arg = var; + var->estate = ExecInitExpr(expr, ps); + var->econtext = ps->ps_ExprContext; + var->mcxt = CurrentMemoryContext; + var->evaluated = false; + var->value = (Datum) 0; + var->isnull = true; + + args = lappend(args, var); + } + } + + cxt->colexprs = palloc(sizeof(*cxt->colexprs) * + list_length(tf->colvalexprs)); + + i = 0; + + foreach(lc, tf->colvalexprs) + { + Expr *expr = lfirst(lc); + + cxt->colexprs[i++].expr = ExecInitExpr(expr, ps); + } + + JsonTableInitScanState(cxt, &cxt->root, root, NULL, args, + CurrentMemoryContext); + + state->opaque = cxt; +} + +/* Reset scan iterator to the beginning of the item list */ +static void +JsonTableRescan(JsonTableScanState *scan) +{ + memset(&scan->iter, 0, sizeof(scan->iter)); + scan->current = PointerGetDatum(NULL); + scan->advanceNested = false; + scan->ordinal = 0; +} + +/* Reset context item of a scan, execute JSON path and reset a scan */ +static void +JsonTableResetContextItem(JsonTableScanState *scan, Datum item) +{ + MemoryContext oldcxt; + JsonPathExecResult res; + + JsonValueListClear(&scan->found); + + MemoryContextResetOnly(scan->mcxt); + + oldcxt = MemoryContextSwitchTo(scan->mcxt); + + res = executeJsonPath(scan->path, scan->args, DatumGetJsonbP(item), + &scan->found); + + MemoryContextSwitchTo(oldcxt); + + if (jperIsError(res)) + { + if (scan->errorOnError) + throwJsonPathError(res); /* does not return */ + else + JsonValueListClear(&scan->found); /* EMPTY ON ERROR case */ + } + + JsonTableRescan(scan); +} + +/* + * JsonTableSetDocument + * Install the input document + */ +static void +JsonTableSetDocument(TableFuncScanState *state, Datum value) +{ + JsonTableContext *cxt = GetJsonTableContext(state, "JsonTableSetDocument"); + + JsonTableResetContextItem(&cxt->root, value); +} + +/* Recursively reset scan and its child nodes */ +static void +JsonTableRescanRecursive(JsonTableJoinState *state) +{ + if (state->is_join) + { + JsonTableRescanRecursive(state->u.join.left); + JsonTableRescanRecursive(state->u.join.right); + state->u.join.advanceRight = false; + } + else + { + JsonTableRescan(&state->u.scan); + if (state->u.scan.nested) + JsonTableRescanRecursive(state->u.scan.nested); + } +} + +/* + * Fetch next row from a cross/union joined scan. + * + * Returned false at the end of a scan, true otherwise. + */ +static bool +JsonTableNextJoinRow(JsonTableJoinState *state) +{ + if (!state->is_join) + return JsonTableNextRow(&state->u.scan); + + if (state->u.join.advanceRight) + { + /* fetch next inner row */ + if (JsonTableNextJoinRow(state->u.join.right)) + return true; + + /* inner rows are exhausted */ + if (state->u.join.cross) + state->u.join.advanceRight = false; /* next outer row */ + else + return false; /* end of scan */ + } + + while (!state->u.join.advanceRight) + { + /* fetch next outer row */ + bool left = JsonTableNextJoinRow(state->u.join.left); + + if (state->u.join.cross) + { + if (!left) + return false; /* end of scan */ + + JsonTableRescanRecursive(state->u.join.right); + + if (!JsonTableNextJoinRow(state->u.join.right)) + continue; /* next outer row */ + + state->u.join.advanceRight = true; /* next inner row */ + } + else if (!left) + { + if (!JsonTableNextJoinRow(state->u.join.right)) + return false; /* end of scan */ + + state->u.join.advanceRight = true; /* next inner row */ + } + + break; + } + + return true; +} + +/* Recursively set 'reset' flag of scan and its child nodes */ +static void +JsonTableJoinReset(JsonTableJoinState *state) +{ + if (state->is_join) + { + JsonTableJoinReset(state->u.join.left); + JsonTableJoinReset(state->u.join.right); + state->u.join.advanceRight = false; + } + else + { + state->u.scan.reset = true; + state->u.scan.advanceNested = false; + + if (state->u.scan.nested) + JsonTableJoinReset(state->u.scan.nested); + } +} + +/* + * Fetch next row from a simple scan with outer/inner joined nested subscans. + * + * Returned false at the end of a scan, true otherwise. + */ +static bool +JsonTableNextRow(JsonTableScanState *scan) +{ + /* reset context item if requested */ + if (scan->reset) + { + JsonTableResetContextItem(scan, scan->parent->current); + scan->reset = false; + } + + if (scan->advanceNested) + { + /* fetch next nested row */ + scan->advanceNested = JsonTableNextJoinRow(scan->nested); + + if (scan->advanceNested) + return true; + } + + for (;;) + { + /* fetch next row */ + JsonbValue *jbv = JsonValueListNext(&scan->found, &scan->iter); + MemoryContext oldcxt; + + if (!jbv) + { + scan->current = PointerGetDatum(NULL); + return false; /* end of scan */ + } + + /* set current row item */ + oldcxt = MemoryContextSwitchTo(scan->mcxt); + scan->current = JsonbPGetDatum(JsonbValueToJsonb(jbv)); + MemoryContextSwitchTo(oldcxt); + + scan->ordinal++; + + if (!scan->nested) + break; + + JsonTableJoinReset(scan->nested); + + scan->advanceNested = JsonTableNextJoinRow(scan->nested); + + if (scan->advanceNested || scan->outerJoin) + break; + + /* state->ordinal--; */ /* skip current outer row, reset counter */ + } + + return true; +} + +/* + * JsonTableFetchRow + * Prepare the next "current" tuple for upcoming GetValue calls. + * Returns FALSE if the row-filter expression returned no more rows. + */ +static bool +JsonTableFetchRow(TableFuncScanState *state) +{ + JsonTableContext *cxt = GetJsonTableContext(state, "JsonTableFetchRow"); + + if (cxt->empty) + return false; + + return JsonTableNextRow(&cxt->root); +} + +/* + * JsonTableGetValue + * Return the value for column number 'colnum' for the current row. + * + * This leaks memory, so be sure to reset often the context in which it's + * called. + */ +static Datum +JsonTableGetValue(TableFuncScanState *state, int colnum, + Oid typid, int32 typmod, bool *isnull) +{ + JsonTableContext *cxt = GetJsonTableContext(state, "JsonTableGetValue"); + ExprContext *econtext = state->ss.ps.ps_ExprContext; + ExprState *estate = cxt->colexprs[colnum].expr; + JsonTableScanState *scan = cxt->colexprs[colnum].scan; + Datum result; + + if (!DatumGetPointer(scan->current)) /* NULL from outer/union join */ + { + result = (Datum) 0; + *isnull = true; + } + else if (estate) /* regular column */ + { + result = ExecEvalExprPassingCaseValue(estate, econtext, isnull, + scan->current, false); + } + else + { + result = Int32GetDatum(scan->ordinal); /* ordinality column */ + *isnull = false; + } + + return result; +} + +/* + * JsonTableDestroyOpaque + */ +static void +JsonTableDestroyOpaque(TableFuncScanState *state) +{ + JsonTableContext *cxt = GetJsonTableContext(state, "JsonTableDestroyOpaque"); + + /* not valid anymore */ + cxt->magic = 0; + + state->opaque = NULL; +} + +const TableFuncRoutine JsonbTableRoutine = +{ + JsonTableInitOpaque, + JsonTableSetDocument, + NULL, + NULL, + NULL, + JsonTableFetchRow, + JsonTableGetValue, + JsonTableDestroyOpaque +}; diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index a167232..58ed084 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -467,6 +467,8 @@ static text *string_to_text(char *str); static char *flatten_reloptions(Oid relid); static void get_json_path_spec(Node *path_spec, deparse_context *context, bool showimplicit); +static void get_json_table_columns(TableFunc *tf, JsonTableParentNode *node, + deparse_context *context, bool showimplicit); #define only_marker(rte) ((rte)->inh ? "" : "ONLY ") @@ -8903,6 +8905,9 @@ get_rule_expr(Node *node, deparse_context *context, case IS_JSON_EXISTS: appendStringInfoString(buf, "JSON_EXISTS("); break; + default: + elog(ERROR, "unexpected JsonExpr type: %d", jexpr->op); + break; } get_rule_expr(jexpr->raw_expr, context, showimplicit); @@ -9937,16 +9942,14 @@ get_sublink_expr(SubLink *sublink, deparse_context *context) /* ---------- - * get_tablefunc - Parse back a table function + * get_xmltable - Parse back a XMLTABLE function * ---------- */ static void -get_tablefunc(TableFunc *tf, deparse_context *context, bool showimplicit) +get_xmltable(TableFunc *tf, deparse_context *context, bool showimplicit) { StringInfo buf = context->buf; - /* XMLTABLE is the only existing implementation. */ - appendStringInfoString(buf, "XMLTABLE("); if (tf->ns_uris != NIL) @@ -10050,6 +10053,280 @@ get_tablefunc(TableFunc *tf, deparse_context *context, bool showimplicit) appendStringInfoChar(buf, ')'); } +/* + * get_json_nested_columns - Parse back nested JSON_TABLE columns + */ +static void +get_json_table_nested_columns(TableFunc *tf, Node *node, + deparse_context *context, bool showimplicit, + bool needcomma) +{ + if (IsA(node, JsonTableSiblingNode)) + { + JsonTableSiblingNode *n = (JsonTableSiblingNode *) node; + + get_json_table_nested_columns(tf, n->larg, context, showimplicit, + needcomma); + get_json_table_nested_columns(tf, n->rarg, context, showimplicit, true); + } + else + { + JsonTableParentNode *n = castNode(JsonTableParentNode, node); + + if (needcomma) + appendStringInfoChar(context->buf, ','); + + appendStringInfoChar(context->buf, ' '); + appendContextKeyword(context, "NESTED PATH ", 0, 0, 0); + get_const_expr(n->path, context, -1); + appendStringInfo(context->buf, " AS %s", quote_identifier(n->name)); + get_json_table_columns(tf, n, context, showimplicit); + } +} + +/* + * get_json_table_plan - Parse back a JSON_TABLE plan + */ +static void +get_json_table_plan(TableFunc *tf, Node *node, deparse_context *context, + bool parenthesize) +{ + if (parenthesize) + appendStringInfoChar(context->buf, '('); + + if (IsA(node, JsonTableSiblingNode)) + { + JsonTableSiblingNode *n = (JsonTableSiblingNode *) node; + + get_json_table_plan(tf, n->larg, context, + IsA(n->larg, JsonTableSiblingNode) || + castNode(JsonTableParentNode, n->larg)->child); + + appendStringInfoString(context->buf, n->cross ? " CROSS " : " UNION "); + + get_json_table_plan(tf, n->rarg, context, + IsA(n->rarg, JsonTableSiblingNode) || + castNode(JsonTableParentNode, n->rarg)->child); + } + else + { + JsonTableParentNode *n = castNode(JsonTableParentNode, node); + + appendStringInfoString(context->buf, quote_identifier(n->name)); + + if (n->child) + { + appendStringInfoString(context->buf, + n->outerJoin ? " OUTER " : " INNER "); + get_json_table_plan(tf, n->child, context, + IsA(n->child, JsonTableSiblingNode)); + } + } + + if (parenthesize) + appendStringInfoChar(context->buf, ')'); +} + +/* + * get_json_table_columns - Parse back JSON_TABLE columns + */ +static void +get_json_table_columns(TableFunc *tf, JsonTableParentNode *node, + deparse_context *context, bool showimplicit) +{ + StringInfo buf = context->buf; + ListCell *l1; + ListCell *l2; + ListCell *l3; + ListCell *l4; + int colnum = 0; + + l2 = list_head(tf->coltypes); + l3 = list_head(tf->coltypmods); + l4 = list_head(tf->colvalexprs); + + appendStringInfoChar(buf, ' '); + appendContextKeyword(context, "COLUMNS (", 0, 0, 0); + + if (PRETTY_INDENT(context)) + context->indentLevel += PRETTYINDENT_VAR; + + foreach(l1, tf->colnames) + { + char *colname = strVal(lfirst(l1)); + JsonExpr *colexpr; + Oid typid; + int32 typmod; + bool ordinality; + + typid = lfirst_oid(l2); + l2 = lnext(l2); + typmod = lfirst_int(l3); + l3 = lnext(l3); + colexpr = castNode(JsonExpr, lfirst(l4)); + l4 = lnext(l4); + + if (colnum < node->colMin) + { + colnum++; + continue; + } + + if (colnum > node->colMax) + break; + + if (colnum > node->colMin) + appendStringInfoString(buf, ", "); + + colnum++; + + ordinality = !colexpr; + + appendContextKeyword(context, "", 0, 0, 0); + + appendStringInfo(buf, "%s %s", quote_identifier(colname), + ordinality ? "FOR ORDINALITY" : + format_type_with_typemod(typid, typmod)); + if (ordinality) + continue; + + if (colexpr->op == IS_JSON_QUERY) + appendStringInfoString(buf, + colexpr->format.type == JS_FORMAT_JSONB ? + " FORMAT JSONB" : " FORMAT JSON"); + + appendStringInfoString(buf, " PATH "); + + get_json_path_spec(colexpr->path_spec, context, showimplicit); + + if (colexpr->wrapper == JSW_CONDITIONAL) + appendStringInfo(buf, " WITH CONDITIONAL WRAPPER"); + + if (colexpr->wrapper == JSW_UNCONDITIONAL) + appendStringInfo(buf, " WITH UNCONDITIONAL WRAPPER"); + + if (colexpr->omit_quotes) + appendStringInfo(buf, " OMIT QUOTES"); + + if (colexpr->on_empty.btype != JSON_BEHAVIOR_NULL) + get_json_behavior(&colexpr->on_empty, context, "EMPTY"); + + if (colexpr->on_error.btype != JSON_BEHAVIOR_NULL) + get_json_behavior(&colexpr->on_error, context, "ERROR"); + } + + if (node->child) + get_json_table_nested_columns(tf, node->child, context, showimplicit, + node->colMax >= node->colMin); + + if (PRETTY_INDENT(context)) + context->indentLevel -= PRETTYINDENT_VAR; + + appendContextKeyword(context, ")", 0, 0, 0); +} + +/* ---------- + * get_json_table - Parse back a JSON_TABLE function + * ---------- + */ +static void +get_json_table(TableFunc *tf, deparse_context *context, bool showimplicit) +{ + StringInfo buf = context->buf; + JsonExpr *jexpr = castNode(JsonExpr, tf->docexpr); + JsonTableParentNode *root = castNode(JsonTableParentNode, tf->plan); + + appendStringInfoString(buf, "JSON_TABLE("); + + if (PRETTY_INDENT(context)) + context->indentLevel += PRETTYINDENT_VAR; + + appendContextKeyword(context, "", 0, 0, 0); + + get_rule_expr(jexpr->raw_expr, context, showimplicit); + + if (jexpr->format.type != JS_FORMAT_DEFAULT) + { + appendStringInfoString(buf, + jexpr->format.type == JS_FORMAT_JSONB ? + " FORMAT JSONB" : " FORMAT JSON"); + + if (jexpr->format.encoding != JS_ENC_DEFAULT) + { + const char *encoding = + jexpr->format.encoding == JS_ENC_UTF16 ? "UTF16" : + jexpr->format.encoding == JS_ENC_UTF32 ? "UTF32" : + "UTF8"; + + appendStringInfo(buf, " ENCODING %s", encoding); + } + } + + appendStringInfoString(buf, ", "); + + get_const_expr(root->path, context, -1); + + appendStringInfo(buf, " AS %s", quote_identifier(root->name)); + + if (jexpr->passing.values) + { + ListCell *lc1, *lc2; + bool needcomma = false; + + appendStringInfoChar(buf, ' '); + appendContextKeyword(context, "PASSING ", 0, 0, 0); + + if (PRETTY_INDENT(context)) + context->indentLevel += PRETTYINDENT_VAR; + + forboth(lc1, jexpr->passing.names, + lc2, jexpr->passing.values) + { + if (needcomma) + appendStringInfoString(buf, ", "); + needcomma = true; + + appendContextKeyword(context, "", 0, 0, 0); + + get_rule_expr((Node *) lfirst(lc2), context, false); + appendStringInfo(buf, " AS %s", + quote_identifier(((Value *) lfirst(lc1))->val.str)); + } + + if (PRETTY_INDENT(context)) + context->indentLevel -= PRETTYINDENT_VAR; + } + + get_json_table_columns(tf, root, context, showimplicit); + + appendStringInfoChar(buf, ' '); + appendContextKeyword(context, "PLAN ", 0, 0, 0); + get_json_table_plan(tf, (Node *) root, context, true); + + if (jexpr->on_error.btype != JSON_BEHAVIOR_EMPTY) + get_json_behavior(&jexpr->on_error, context, "ERROR"); + + if (PRETTY_INDENT(context)) + context->indentLevel -= PRETTYINDENT_VAR; + + appendContextKeyword(context, ")", 0, 0, 0); +} + +/* ---------- + * get_tablefunc - Parse back a table function + * ---------- + */ +static void +get_tablefunc(TableFunc *tf, deparse_context *context, bool showimplicit) +{ + /* XMLTABLE and JSON_TABLE are the only existing implementations. */ + + if (tf->functype == TFT_XMLTABLE) + get_xmltable(tf, context, showimplicit); + else if (tf->functype == TFT_JSON_TABLE) + get_json_table(tf, context, showimplicit); +} + /* ---------- * get_from_clause - Parse back a FROM clause * diff --git a/src/include/executor/execExpr.h b/src/include/executor/execExpr.h index f30bf1f..8712a27 100644 --- a/src/include/executor/execExpr.h +++ b/src/include/executor/execExpr.h @@ -786,6 +786,9 @@ extern Datum ExecPrepareJsonItemCoercion(struct JsonbValue *item, bool is_jsonb, JsonReturning *returning, struct JsonCoercionsState *coercions, struct JsonCoercionState **pjcstate); +extern Datum ExecEvalExprPassingCaseValue(ExprState *estate, + ExprContext *econtext, bool *isnull, + Datum caseval_datum, bool caseval_isnull); extern void ExecAggInitGroup(AggState *aggstate, AggStatePerTrans pertrans, AggStatePerGroup pergroup); extern Datum ExecAggTransReparent(AggState *aggstate, AggStatePerTrans pertrans, diff --git a/src/include/nodes/makefuncs.h b/src/include/nodes/makefuncs.h index f7aec03..78488bf 100644 --- a/src/include/nodes/makefuncs.h +++ b/src/include/nodes/makefuncs.h @@ -90,6 +90,8 @@ extern VacuumRelation *makeVacuumRelation(RangeVar *relation, Oid oid, List *va_ extern JsonValueExpr *makeJsonValueExpr(Expr *expr, JsonFormat format); extern JsonBehavior *makeJsonBehavior(JsonBehaviorType type, Node *expr); +extern Node *makeJsonTableJoinedPlan(JsonTablePlanJoinType type, + Node *plan1, Node *plan2, int location); extern Node *makeJsonKeyValue(Node *key, Node *value); extern Node *makeJsonIsPredicate(Node *expr, JsonFormat format, JsonValueType vtype, bool unique_keys); diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h index 14c387a..e107111 100644 --- a/src/include/nodes/nodes.h +++ b/src/include/nodes/nodes.h @@ -194,6 +194,8 @@ typedef enum NodeTag T_JsonExpr, T_JsonCoercion, T_JsonItemCoercions, + T_JsonTableParentNode, + T_JsonTableSiblingNode, /* * TAGS FOR EXPRESSION STATE NODES (execnodes.h) @@ -483,6 +485,9 @@ typedef enum NodeTag T_JsonFuncExpr, T_JsonIsPredicate, T_JsonExistsPredicate, + T_JsonTable, + T_JsonTableColumn, + T_JsonTablePlan, T_JsonCommon, T_JsonArgument, T_JsonKeyValue, diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 5c2585e..be502f5 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -1440,6 +1440,18 @@ typedef enum JsonQuotes } JsonQuotes; /* + * JsonTableColumnType - + * enumeration of JSON_TABLE column types + */ +typedef enum +{ + JTC_FOR_ORDINALITY, + JTC_REGULAR, + JTC_FORMATTED, + JTC_NESTED, +} JsonTableColumnType; + +/* * JsonPathSpec - * representation of JSON path constant */ @@ -1510,6 +1522,83 @@ typedef struct JsonFuncExpr } JsonFuncExpr; /* + * JsonTableColumn - + * untransformed representation of JSON_TABLE column + */ +typedef struct JsonTableColumn +{ + NodeTag type; + JsonTableColumnType coltype; /* column type */ + char *name; /* column name */ + TypeName *typename; /* column type name */ + JsonPathSpec pathspec; /* path specification, if any */ + char *pathname; /* path name, if any */ + JsonFormat format; /* JSON format clause, if specified */ + JsonWrapper wrapper; /* WRAPPER behavior for formatted columns */ + bool omit_quotes; /* omit or keep quotes on scalar strings? */ + List *columns; /* nested columns */ + JsonBehavior *on_empty; /* ON EMPTY behavior */ + JsonBehavior *on_error; /* ON ERROR behavior */ + int location; /* token location, or -1 if unknown */ +} JsonTableColumn; + +/* + * JsonTablePlanType - + * flags for JSON_TABLE plan node types representation + */ +typedef enum JsonTablePlanType +{ + JSTP_DEFAULT, + JSTP_SIMPLE, + JSTP_JOINED, +} JsonTablePlanType; + +/* + * JsonTablePlanJoinType - + * flags for JSON_TABLE join types representation + */ +typedef enum JsonTablePlanJoinType +{ + JSTP_INNER = 0x01, + JSTP_OUTER = 0x02, + JSTP_CROSS = 0x04, + JSTP_UNION = 0x08, +} JsonTablePlanJoinType; + +typedef struct JsonTablePlan JsonTablePlan; + +/* + * JsonTablePlan - + * untransformed representation of JSON_TABLE plan node + */ +struct JsonTablePlan +{ + NodeTag type; + JsonTablePlanType plan_type; /* plan type */ + JsonTablePlanJoinType join_type; /* join type (for joined plan only) */ + JsonTablePlan *plan1; /* first joined plan */ + JsonTablePlan *plan2; /* second joined plan */ + char *pathname; /* path name (for simple plan only) */ + int location; /* token location, or -1 if unknown */ +}; + +/* + * JsonTable - + * untransformed representation of JSON_TABLE + */ +typedef struct JsonTable +{ + NodeTag type; + JsonCommon *common; /* common JSON path syntax fields */ + List *columns; /* list of JsonTableColumn */ + JsonTablePlan *plan; /* join plan, if specified */ + JsonBehavior *on_error; /* ON ERROR behavior, if specified */ + Alias *alias; /* table alias in FROM clause */ + bool lateral; /* does it have LATERAL prefix? */ + int location; /* token location, or -1 if unknown */ +} JsonTable; + +/* * JsonValueType - * representation of JSON item type in IS JSON predicate */ diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h index 4bfa016..19745dc 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -73,12 +73,19 @@ typedef struct RangeVar int location; /* token location, or -1 if unknown */ } RangeVar; +typedef enum TableFuncType +{ + TFT_XMLTABLE, + TFT_JSON_TABLE +} TableFuncType; + /* * TableFunc - node for a table function, such as XMLTABLE. */ typedef struct TableFunc { NodeTag type; + TableFuncType functype; /* XMLTABLE or JSON_TABLE */ List *ns_uris; /* list of namespace uri */ List *ns_names; /* list of namespace names */ Node *docexpr; /* input document expression */ @@ -89,7 +96,9 @@ typedef struct TableFunc List *colcollations; /* OID list of column collation OIDs */ List *colexprs; /* list of column filter expressions */ List *coldefexprs; /* list of column default expressions */ + List *colvalexprs; /* list of column value expressions */ Bitmapset *notnulls; /* nullability flag for each output column */ + Node *plan; /* JSON_TABLE plan */ int ordinalitycol; /* counts from 0; -1 if none specified */ int location; /* token location, or -1 if unknown */ } TableFunc; @@ -1182,7 +1191,8 @@ typedef enum JsonExprOp { IS_JSON_VALUE, /* JSON_VALUE() */ IS_JSON_QUERY, /* JSON_QUERY() */ - IS_JSON_EXISTS /* JSON_EXISTS() */ + IS_JSON_EXISTS, /* JSON_EXISTS() */ + IS_JSON_TABLE /* JSON_TABLE() */ } JsonExprOp; /* @@ -1335,6 +1345,35 @@ typedef struct JsonExpr int location; /* token location, or -1 if unknown */ } JsonExpr; +/* + * JsonTableParentNode - + * transformed representation of parent JSON_TABLE plan node + */ +typedef struct JsonTableParentNode +{ + NodeTag type; + Const *path; /* jsonpath constant */ + char *name; /* path name */ + JsonPassing passing; /* PASSING arguments */ + Node *child; /* nested columns, if any */ + bool outerJoin; /* outer or inner join for nested columns? */ + int colMin; /* min column index in the resulting column list */ + int colMax; /* max column index in the resulting column list */ + bool errorOnError; /* ERROR/EMPTY ON ERROR behavior */ +} JsonTableParentNode; + +/* + * JsonTableSiblingNode - + * transformed representation of joined sibling JSON_TABLE plan node + */ +typedef struct JsonTableSiblingNode +{ + NodeTag type; + Node *larg; /* left join node */ + Node *rarg; /* right join node */ + bool cross; /* cross or union join? */ +} JsonTableSiblingNode; + /* ---------------- * NullTest * diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index 233e18d..a68cd2a 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -232,6 +232,7 @@ PG_KEYWORD("json_exists", JSON_EXISTS, COL_NAME_KEYWORD) PG_KEYWORD("json_object", JSON_OBJECT, COL_NAME_KEYWORD) PG_KEYWORD("json_objectagg", JSON_OBJECTAGG, COL_NAME_KEYWORD) PG_KEYWORD("json_query", JSON_QUERY, COL_NAME_KEYWORD) +PG_KEYWORD("json_table", JSON_TABLE, COL_NAME_KEYWORD) PG_KEYWORD("json_value", JSON_VALUE, COL_NAME_KEYWORD) PG_KEYWORD("keep", KEEP, UNRESERVED_KEYWORD) PG_KEYWORD("key", KEY, UNRESERVED_KEYWORD) @@ -272,6 +273,7 @@ PG_KEYWORD("names", NAMES, UNRESERVED_KEYWORD) PG_KEYWORD("national", NATIONAL, COL_NAME_KEYWORD) PG_KEYWORD("natural", NATURAL, TYPE_FUNC_NAME_KEYWORD) PG_KEYWORD("nchar", NCHAR, COL_NAME_KEYWORD) +PG_KEYWORD("nested", NESTED, UNRESERVED_KEYWORD) PG_KEYWORD("new", NEW, UNRESERVED_KEYWORD) PG_KEYWORD("next", NEXT, UNRESERVED_KEYWORD) PG_KEYWORD("no", NO, UNRESERVED_KEYWORD) @@ -315,7 +317,9 @@ PG_KEYWORD("partial", PARTIAL, UNRESERVED_KEYWORD) PG_KEYWORD("partition", PARTITION, UNRESERVED_KEYWORD) PG_KEYWORD("passing", PASSING, UNRESERVED_KEYWORD) PG_KEYWORD("password", PASSWORD, UNRESERVED_KEYWORD) +PG_KEYWORD("path", PATH, UNRESERVED_KEYWORD) PG_KEYWORD("placing", PLACING, RESERVED_KEYWORD) +PG_KEYWORD("plan", PLAN, UNRESERVED_KEYWORD) PG_KEYWORD("plans", PLANS, UNRESERVED_KEYWORD) PG_KEYWORD("policy", POLICY, UNRESERVED_KEYWORD) PG_KEYWORD("position", POSITION, COL_NAME_KEYWORD) diff --git a/src/include/utils/jsonpath.h b/src/include/utils/jsonpath.h index 4e24bd1..8430866 100644 --- a/src/include/utils/jsonpath.h +++ b/src/include/utils/jsonpath.h @@ -15,9 +15,10 @@ #define JSONPATH_H #include "fmgr.h" -#include "utils/jsonb.h" +#include "executor/tablefunc.h" #include "nodes/pg_list.h" #include "nodes/primnodes.h" +#include "utils/jsonb.h" typedef struct { @@ -262,6 +263,7 @@ typedef struct JsonPathVariableEvalContext JsonPathVariable var; struct ExprContext *econtext; struct ExprState *estate; + MemoryContext mcxt; /* memory context for cached value */ Datum value; bool isnull; bool evaluated; @@ -292,4 +294,6 @@ extern Datum JsonPathQuery(Datum json, JsonPath *jp, JsonWrapper wrapper, extern Datum EvalJsonPathVar(void *cxt, bool *isnull); +extern const TableFuncRoutine JsonbTableRoutine; + #endif diff --git a/src/test/regress/expected/json_sqljson.out b/src/test/regress/expected/json_sqljson.out index f7d1568..fc26f27 100644 --- a/src/test/regress/expected/json_sqljson.out +++ b/src/test/regress/expected/json_sqljson.out @@ -1041,6 +1041,11 @@ INSERT INTO test_json_constraints VALUES ('{"a": 10}', 1); ERROR: new row for relation "test_json_constraints" violates check constraint "test_json_constraint4" DETAIL: Failing row contains ({"a": 10}, 1, [1, 2]). DROP TABLE test_json_constraints; +-- JSON_TABLE +SELECT * FROM JSON_TABLE(NULL FORMAT JSON, '$' COLUMNS (foo text)); +ERROR: JSON_TABLE() is not yet implemented for json type +LINE 1: SELECT * FROM JSON_TABLE(NULL FORMAT JSON, '$' COLUMNS (foo ... + ^ -- Extension: non-constant JSON path SELECT JSON_EXISTS(json '{"a": 123}', '$' || '.' || 'a'); json_exists diff --git a/src/test/regress/expected/jsonb_sqljson.out b/src/test/regress/expected/jsonb_sqljson.out index 28c82a1..6d92b17 100644 --- a/src/test/regress/expected/jsonb_sqljson.out +++ b/src/test/regress/expected/jsonb_sqljson.out @@ -918,6 +918,935 @@ INSERT INTO test_jsonb_constraints VALUES ('{"a": 10}', 1); ERROR: new row for relation "test_jsonb_constraints" violates check constraint "test_jsonb_constraint4" DETAIL: Failing row contains ({"a": 10}, 1, [1, 2]). DROP TABLE test_jsonb_constraints; +-- JSON_TABLE +-- Should fail (JSON_TABLE can be used only in FROM clause) +SELECT JSON_TABLE('[]', '$'); +ERROR: syntax error at or near "(" +LINE 1: SELECT JSON_TABLE('[]', '$'); + ^ +-- Should fail (no columns) +SELECT * FROM JSON_TABLE(NULL, '$' COLUMNS ()); +ERROR: syntax error at or near ")" +LINE 1: SELECT * FROM JSON_TABLE(NULL, '$' COLUMNS ()); + ^ +-- NULL => empty table +SELECT * FROM JSON_TABLE(NULL::jsonb, '$' COLUMNS (foo int)) bar; + foo +----- +(0 rows) + +-- +SELECT * FROM JSON_TABLE(jsonb '123', '$' + COLUMNS (item int PATH '$', foo int)) bar; + item | foo +------+----- + 123 | +(1 row) + +-- JSON_TABLE: basic functionality +SELECT * +FROM + (VALUES + ('1'), + ('[]'), + ('{}'), + ('[1, 1.23, "2", "aaaaaaa", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""]') + ) vals(js) + LEFT OUTER JOIN +-- JSON_TABLE is implicitly lateral + JSON_TABLE( + vals.js::jsonb, 'lax $[*]' + COLUMNS ( + id FOR ORDINALITY, + id2 FOR ORDINALITY, -- allowed additional ordinality columns + "int" int PATH '$', + "text" text PATH '$', + "char(4)" char(4) PATH '$', + "bool" bool PATH '$', + "numeric" numeric PATH '$', + js json PATH '$', + jb jsonb PATH '$', + jst text FORMAT JSON PATH '$', + jsc char(4) FORMAT JSON PATH '$', + jsv varchar(4) FORMAT JSON PATH '$', + jsb jsonb FORMAT JSON PATH '$', + aaa int, -- implicit path '$."aaa"', + aaa1 int PATH '$.aaa' + ) + ) jt + ON true; + js | id | id2 | int | text | char(4) | bool | numeric | js | jb | jst | jsc | jsv | jsb | aaa | aaa1 +--------------------------------------------------------------------------------+----+-----+-----+---------+---------+------+---------+-----------+-----------+--------------+------+------+--------------+-----+------ + 1 | 1 | 1 | 1 | 1 | 1 | t | 1 | 1 | 1 | 1 | 1 | 1 | 1 | | + [] | | | | | | | | | | | | | | | + {} | 1 | 1 | | | | | | | | {} | {} | {} | {} | | + [1, 1.23, "2", "aaaaaaa", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 1 | 1 | 1 | 1 | 1 | t | 1 | 1 | 1 | 1 | 1 | 1 | 1 | | + [1, 1.23, "2", "aaaaaaa", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 2 | 2 | 1 | 1.23 | 1.23 | | 1.23 | 1.23 | 1.23 | 1.23 | 1.23 | 1.23 | 1.23 | | + [1, 1.23, "2", "aaaaaaa", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 3 | 3 | 2 | 2 | 2 | | 2 | "2" | "2" | "2" | "2" | "2" | "2" | | + [1, 1.23, "2", "aaaaaaa", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 4 | 4 | | aaaaaaa | aaaa | | | "aaaaaaa" | "aaaaaaa" | "aaaaaaa" | "aaa | "aaa | "aaaaaaa" | | + [1, 1.23, "2", "aaaaaaa", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 5 | 5 | | | | | | | | null | null | null | null | | + [1, 1.23, "2", "aaaaaaa", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 6 | 6 | 0 | false | fals | f | | false | false | false | fals | fals | false | | + [1, 1.23, "2", "aaaaaaa", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 7 | 7 | 1 | true | true | t | | true | true | true | true | true | true | | + [1, 1.23, "2", "aaaaaaa", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 8 | 8 | | | | | | | | {"aaa": 123} | {"aa | {"aa | {"aaa": 123} | 123 | 123 + [1, 1.23, "2", "aaaaaaa", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 9 | 9 | | [1,2] | [1,2 | | | "[1,2]" | "[1,2]" | "[1,2]" | "[1, | "[1, | "[1,2]" | | + [1, 1.23, "2", "aaaaaaa", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 10 | 10 | | "str" | "str | | | "\"str\"" | "\"str\"" | "\"str\"" | "\"s | "\"s | "\"str\"" | | +(13 rows) + +-- JSON_TABLE: Test backward parsing +CREATE VIEW jsonb_table_view AS +SELECT * FROM + JSON_TABLE( + jsonb 'null', 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c" + COLUMNS ( + id FOR ORDINALITY, + id2 FOR ORDINALITY, -- allowed additional ordinality columns + "int" int PATH '$', + "text" text PATH '$', + "char(4)" char(4) PATH '$', + "bool" bool PATH '$', + "numeric" numeric PATH '$', + js json PATH '$', + jb jsonb PATH '$', + jst text FORMAT JSON PATH '$', + jsc char(4) FORMAT JSON PATH '$', + jsv varchar(4) FORMAT JSON PATH '$', + jsb jsonb FORMAT JSON PATH '$', + aaa int, -- implicit path '$."aaa"', + aaa1 int PATH '$.aaa', + NESTED PATH '$[1]' AS p1 COLUMNS ( + a1 int, + NESTED PATH '$[*]' AS "p1 1" COLUMNS ( + a11 text + ), + b1 text + ), + NESTED PATH '$[2]' AS p2 COLUMNS ( + NESTED PATH '$[*]' AS "p2:1" COLUMNS ( + a21 text + ), + NESTED PATH '$[*]' AS p22 COLUMNS ( + a22 text + ) + ) + ) + ); +\sv jsonb_table_view +CREATE OR REPLACE VIEW public.jsonb_table_view AS + SELECT "json_table".id, + "json_table".id2, + "json_table"."int", + "json_table".text, + "json_table"."char(4)", + "json_table".bool, + "json_table"."numeric", + "json_table".js, + "json_table".jb, + "json_table".jst, + "json_table".jsc, + "json_table".jsv, + "json_table".jsb, + "json_table".aaa, + "json_table".aaa1, + "json_table".a1, + "json_table".b1, + "json_table".a11, + "json_table".a21, + "json_table".a22 + FROM JSON_TABLE( + 'null'::jsonb, '$[*]' AS json_table_path_1 + PASSING + 1 + 2 AS a, + '"foo"'::json AS "b c" + COLUMNS ( + id FOR ORDINALITY, + id2 FOR ORDINALITY, + "int" integer PATH '$', + text text PATH '$', + "char(4)" character(4) PATH '$', + bool boolean PATH '$', + "numeric" numeric PATH '$', + js json PATH '$', + jb jsonb PATH '$', + jst text FORMAT JSON PATH '$', + jsc character(4) FORMAT JSON PATH '$', + jsv character varying(4) FORMAT JSON PATH '$', + jsb jsonb FORMAT JSON PATH '$', + aaa integer PATH '$."aaa"', + aaa1 integer PATH '$."aaa"', + NESTED PATH '$[1]' AS p1 + COLUMNS ( + a1 integer PATH '$."a1"', + b1 text PATH '$."b1"', + NESTED PATH '$[*]' AS "p1 1" + COLUMNS ( + a11 text PATH '$."a11"' + ) + ), + NESTED PATH '$[2]' AS p2 + COLUMNS ( + NESTED PATH '$[*]' AS "p2:1" + COLUMNS ( + a21 text PATH '$."a21"' + ), + NESTED PATH '$[*]' AS p22 + COLUMNS ( + a22 text PATH '$."a22"' + ) + ) + ) + PLAN (json_table_path_1 OUTER ((p1 OUTER "p1 1") UNION (p2 OUTER ("p2:1" UNION p22)))) + ) +EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view; + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + Table Function Scan on "json_table" + Output: "json_table".id, "json_table".id2, "json_table"."int", "json_table".text, "json_table"."char(4)", "json_table".bool, "json_table"."numeric", "json_table".js, "json_table".jb, "json_table".jst, "json_table".jsc, "json_table".jsv, "json_table".jsb, "json_table".aaa, "json_table".aaa1, "json_table".a1, "json_table".b1, "json_table".a11, "json_table".a21, "json_table".a22 + Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_1 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (id FOR ORDINALITY, id2 FOR ORDINALITY, "int" integer PATH '$', text text PATH '$', "char(4)" character(4) PATH '$', bool boolean PATH '$', "numeric" numeric PATH '$', js json PATH '$', jb jsonb PATH '$', jst text FORMAT JSON PATH '$', jsc character(4) FORMAT JSON PATH '$', jsv character varying(4) FORMAT JSON PATH '$', jsb jsonb FORMAT JSON PATH '$', aaa integer PATH '$."aaa"', aaa1 integer PATH '$."aaa"', NESTED PATH '$[1]' AS p1 COLUMNS (a1 integer PATH '$."a1"', b1 text PATH '$."b1"', NESTED PATH '$[*]' AS "p1 1" COLUMNS (a11 text PATH '$."a11"')), NESTED PATH '$[2]' AS p2 COLUMNS ( NESTED PATH '$[*]' AS "p2:1" COLUMNS (a21 text PATH '$."a21"'), NESTED PATH '$[*]' AS p22 COLUMNS (a22 text PATH '$."a22"'))) PLAN (json_table_path_1 OUTER ((p1 OUTER "p1 1") UNION (p2 OUTER ("p2:1" UNION p22))))) +(3 rows) + +-- JSON_TABLE: ON EMPTY/ON ERROR behavior +SELECT * +FROM + (VALUES ('1'), ('"err"')) vals(js), + JSON_TABLE(vals.js::jsonb, '$' COLUMNS (a int PATH '$')) jt; + js | a +-------+--- + 1 | 1 + "err" | +(2 rows) + +SELECT * +FROM + (VALUES ('1'), ('"err"')) vals(js) + LEFT OUTER JOIN + JSON_TABLE(vals.js::jsonb, '$' COLUMNS (a int PATH '$') ERROR ON ERROR) jt + ON true; +ERROR: invalid input syntax for integer: "err" +SELECT * +FROM + (VALUES ('1'), ('"err"')) vals(js) + LEFT OUTER JOIN + JSON_TABLE(vals.js::jsonb, '$' COLUMNS (a int PATH '$' ERROR ON ERROR)) jt + ON true; +ERROR: invalid input syntax for integer: "err" +SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH '$.a' ERROR ON EMPTY)) jt; + a +--- + +(1 row) + +SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH 'strict $.a' ERROR ON EMPTY) ERROR ON ERROR) jt; +ERROR: SQL/JSON member not found +SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH 'lax $.a' ERROR ON EMPTY) ERROR ON ERROR) jt; +ERROR: no SQL/JSON item +SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH '$' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt; + a +--- + 2 +(1 row) + +SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH 'strict $.a' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt; + a +--- + 2 +(1 row) + +SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH 'lax $.a' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt; + a +--- + 1 +(1 row) + +-- JSON_TABLE: nested paths and plans +-- Should fail (JSON_TABLE columns shall contain explicit AS path +-- specifications if explicit PLAN clause is used) +SELECT * FROM JSON_TABLE( + jsonb '[]', '$' -- AS required here + COLUMNS ( + foo int PATH '$' + ) + PLAN DEFAULT (UNION) +) jt; +ERROR: invalid JSON_TABLE expression +LINE 2: jsonb '[]', '$' + ^ +DETAIL: JSON_TABLE columns shall contain explicit AS pathname specification if explicit PLAN clause is used +SELECT * FROM JSON_TABLE( + jsonb '[]', '$' AS path1 + COLUMNS ( + NESTED PATH '$' COLUMNS ( -- AS required here + foo int PATH '$' + ) + ) + PLAN DEFAULT (UNION) +) jt; +ERROR: invalid JSON_TABLE expression +LINE 4: NESTED PATH '$' COLUMNS ( + ^ +DETAIL: JSON_TABLE columns shall contain explicit AS pathname specification if explicit PLAN clause is used +-- Should fail (column names anf path names shall be distinct) +SELECT * FROM JSON_TABLE( + jsonb '[]', '$' AS a + COLUMNS ( + a int + ) +) jt; +ERROR: duplicate JSON_TABLE column name: a +HINT: JSON_TABLE path names and column names shall be distinct from one another +SELECT * FROM JSON_TABLE( + jsonb '[]', '$' AS a + COLUMNS ( + b int, + NESTED PATH '$' AS a + COLUMNS ( + c int + ) + ) +) jt; +ERROR: duplicate JSON_TABLE column name: a +HINT: JSON_TABLE path names and column names shall be distinct from one another +SELECT * FROM JSON_TABLE( + jsonb '[]', '$' + COLUMNS ( + b int, + NESTED PATH '$' AS b + COLUMNS ( + c int + ) + ) +) jt; +ERROR: duplicate JSON_TABLE column name: b +HINT: JSON_TABLE path names and column names shall be distinct from one another +SELECT * FROM JSON_TABLE( + jsonb '[]', '$' + COLUMNS ( + NESTED PATH '$' AS a + COLUMNS ( + b int + ), + NESTED PATH '$' + COLUMNS ( + NESTED PATH '$' AS a + COLUMNS ( + c int + ) + ) + ) +) jt; +ERROR: duplicate JSON_TABLE column name: a +HINT: JSON_TABLE path names and column names shall be distinct from one another +-- JSON_TABLE: plan validation +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p1) +) jt; +ERROR: invalid JSON_TABLE plan +LINE 12: PLAN (p1) + ^ +DETAIL: path name mismatch: expected p0 but p1 is given +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0) +) jt; +ERROR: invalid JSON_TABLE plan +LINE 4: NESTED PATH '$' AS p1 COLUMNS ( + ^ +DETAIL: plan node for nested path p1 was not found in plan +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 OUTER p3) +) jt; +ERROR: invalid JSON_TABLE plan +LINE 4: NESTED PATH '$' AS p1 COLUMNS ( + ^ +DETAIL: plan node for nested path p1 was not found in plan +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 OUTER (p1 CROSS p13)) +) jt; +ERROR: invalid JSON_TABLE plan +LINE 8: NESTED PATH '$' AS p2 COLUMNS ( + ^ +DETAIL: plan node for nested path p2 was not found in plan +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 OUTER (p1 CROSS p2)) +) jt; +ERROR: invalid JSON_TABLE plan +LINE 5: NESTED PATH '$' AS p11 COLUMNS ( foo int ), + ^ +DETAIL: plan node for nested path p11 was not found in plan +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 OUTER ((p1 UNION p11) CROSS p2)) +) jt; +ERROR: invalid JSON_TABLE plan +LINE 12: PLAN (p0 OUTER ((p1 UNION p11) CROSS p2)) + ^ +DETAIL: plan node contains some extra or duplicate sibling nodes +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 OUTER ((p1 INNER p11) CROSS p2)) +) jt; +ERROR: invalid JSON_TABLE plan +LINE 6: NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ^ +DETAIL: plan node for nested path p12 was not found in plan +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 OUTER ((p1 INNER (p12 CROSS p11)) CROSS p2)) +) jt; +ERROR: invalid JSON_TABLE plan +LINE 9: NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ^ +DETAIL: plan node for nested path p21 was not found in plan +SELECT * FROM JSON_TABLE( + jsonb 'null', 'strict $[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 OUTER ((p1 INNER (p12 CROSS p11)) CROSS (p2 INNER p21))) +) jt; + bar | foo | baz +-----+-----+----- +(0 rows) + +SELECT * FROM JSON_TABLE( + jsonb 'null', 'strict $[*]' -- without root path name + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN ((p1 INNER (p12 CROSS p11)) CROSS (p2 INNER p21)) +) jt; + bar | foo | baz +-----+-----+----- +(0 rows) + +-- JSON_TABLE: plan execution +CREATE TEMP TABLE jsonb_table_test (js jsonb); +INSERT INTO jsonb_table_test +VALUES ( + '[ + {"a": 1, "b": [], "c": []}, + {"a": 2, "b": [1, 2, 3], "c": [10, null, 20]}, + {"a": 3, "b": [1, 2], "c": []}, + {"x": "4", "b": [1, 2], "c": 123} + ]' +); +-- unspecified plan (outer, union) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + ) jt; + n | a | b | c +---+----+---+---- + 1 | 1 | | + 2 | 2 | 1 | + 2 | 2 | 2 | + 2 | 2 | 3 | + 2 | 2 | | 10 + 2 | 2 | | + 2 | 2 | | 20 + 3 | 3 | 1 | + 3 | 3 | 2 | + 4 | -1 | 1 | + 4 | -1 | 2 | +(11 rows) + +-- default plan (outer, union) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan default (outer, union) + ) jt; + n | a | b | c +---+----+---+---- + 1 | 1 | | + 2 | 2 | 1 | + 2 | 2 | 2 | + 2 | 2 | 3 | + 2 | 2 | | 10 + 2 | 2 | | + 2 | 2 | | 20 + 3 | 3 | 1 | + 3 | 3 | 2 | + 4 | -1 | 1 | + 4 | -1 | 2 | +(11 rows) + +-- specific plan (p outer (pb union pc)) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan (p outer (pb union pc)) + ) jt; + n | a | b | c +---+----+---+---- + 1 | 1 | | + 2 | 2 | 1 | + 2 | 2 | 2 | + 2 | 2 | 3 | + 2 | 2 | | 10 + 2 | 2 | | + 2 | 2 | | 20 + 3 | 3 | 1 | + 3 | 3 | 2 | + 4 | -1 | 1 | + 4 | -1 | 2 | +(11 rows) + +-- specific plan (p outer (pc union pb)) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan (p outer (pc union pb)) + ) jt; + n | a | c | b +---+----+----+--- + 1 | 1 | | + 2 | 2 | 10 | + 2 | 2 | | + 2 | 2 | 20 | + 2 | 2 | | 1 + 2 | 2 | | 2 + 2 | 2 | | 3 + 3 | 3 | | 1 + 3 | 3 | | 2 + 4 | -1 | | 1 + 4 | -1 | | 2 +(11 rows) + +-- default plan (inner, union) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan default (inner) + ) jt; + n | a | b | c +---+----+---+---- + 2 | 2 | 1 | + 2 | 2 | 2 | + 2 | 2 | 3 | + 2 | 2 | | 10 + 2 | 2 | | + 2 | 2 | | 20 + 3 | 3 | 1 | + 3 | 3 | 2 | + 4 | -1 | 1 | + 4 | -1 | 2 | +(10 rows) + +-- specific plan (p inner (pb union pc)) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan (p inner (pb union pc)) + ) jt; + n | a | b | c +---+----+---+---- + 2 | 2 | 1 | + 2 | 2 | 2 | + 2 | 2 | 3 | + 2 | 2 | | 10 + 2 | 2 | | + 2 | 2 | | 20 + 3 | 3 | 1 | + 3 | 3 | 2 | + 4 | -1 | 1 | + 4 | -1 | 2 | +(10 rows) + +-- default plan (inner, cross) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan default (cross, inner) + ) jt; + n | a | b | c +---+---+---+---- + 2 | 2 | 1 | 10 + 2 | 2 | 1 | + 2 | 2 | 1 | 20 + 2 | 2 | 2 | 10 + 2 | 2 | 2 | + 2 | 2 | 2 | 20 + 2 | 2 | 3 | 10 + 2 | 2 | 3 | + 2 | 2 | 3 | 20 +(9 rows) + +-- specific plan (p inner (pb cross pc)) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan (p inner (pb cross pc)) + ) jt; + n | a | b | c +---+---+---+---- + 2 | 2 | 1 | 10 + 2 | 2 | 1 | + 2 | 2 | 1 | 20 + 2 | 2 | 2 | 10 + 2 | 2 | 2 | + 2 | 2 | 2 | 20 + 2 | 2 | 3 | 10 + 2 | 2 | 3 | + 2 | 2 | 3 | 20 +(9 rows) + +-- default plan (outer, cross) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan default (outer, cross) + ) jt; + n | a | b | c +---+----+---+---- + 1 | 1 | | + 2 | 2 | 1 | 10 + 2 | 2 | 1 | + 2 | 2 | 1 | 20 + 2 | 2 | 2 | 10 + 2 | 2 | 2 | + 2 | 2 | 2 | 20 + 2 | 2 | 3 | 10 + 2 | 2 | 3 | + 2 | 2 | 3 | 20 + 3 | 3 | | + 4 | -1 | | +(12 rows) + +-- specific plan (p outer (pb cross pc)) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan (p outer (pb cross pc)) + ) jt; + n | a | b | c +---+----+---+---- + 1 | 1 | | + 2 | 2 | 1 | 10 + 2 | 2 | 1 | + 2 | 2 | 1 | 20 + 2 | 2 | 2 | 10 + 2 | 2 | 2 | + 2 | 2 | 2 | 20 + 2 | 2 | 3 | 10 + 2 | 2 | 3 | + 2 | 2 | 3 | 20 + 3 | 3 | | + 4 | -1 | | +(12 rows) + +select + jt.*, b1 + 100 as b +from + json_table (jsonb + '[ + {"a": 1, "b": [[1, 10], [2], [3, 30, 300]], "c": [1, null, 2]}, + {"a": 2, "b": [10, 20], "c": [1, null, 2]}, + {"x": "3", "b": [11, 22, 33, 44]} + ]', + '$[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on error, + nested path 'strict $.b[*]' as pb columns ( + b text format json path '$', + nested path 'strict $[*]' as pb1 columns ( + b1 int path '$' + ) + ), + nested path 'strict $.c[*]' as pc columns ( + c text format json path '$', + nested path 'strict $[*]' as pc1 columns ( + c1 int path '$' + ) + ) + ) + --plan default(outer, cross) + plan(p outer ((pb inner pb1) cross (pc outer pc1))) + ) jt; + n | a | b | b1 | c | c1 | b +---+---+--------------+-----+------+----+----- + 1 | 1 | [1, 10] | 1 | 1 | | 101 + 1 | 1 | [1, 10] | 1 | null | | 101 + 1 | 1 | [1, 10] | 1 | 2 | | 101 + 1 | 1 | [1, 10] | 10 | 1 | | 110 + 1 | 1 | [1, 10] | 10 | null | | 110 + 1 | 1 | [1, 10] | 10 | 2 | | 110 + 1 | 1 | [2] | 2 | 1 | | 102 + 1 | 1 | [2] | 2 | null | | 102 + 1 | 1 | [2] | 2 | 2 | | 102 + 1 | 1 | [3, 30, 300] | 3 | 1 | | 103 + 1 | 1 | [3, 30, 300] | 3 | null | | 103 + 1 | 1 | [3, 30, 300] | 3 | 2 | | 103 + 1 | 1 | [3, 30, 300] | 30 | 1 | | 130 + 1 | 1 | [3, 30, 300] | 30 | null | | 130 + 1 | 1 | [3, 30, 300] | 30 | 2 | | 130 + 1 | 1 | [3, 30, 300] | 300 | 1 | | 400 + 1 | 1 | [3, 30, 300] | 300 | null | | 400 + 1 | 1 | [3, 30, 300] | 300 | 2 | | 400 + 2 | 2 | | | | | + 3 | | | | | | +(20 rows) + +-- Should succeed (JSON arguments are passed to root and nested paths) +SELECT * +FROM + generate_series(1, 4) x, + generate_series(1, 3) y, + JSON_TABLE(jsonb + '[[1,2,3],[2,3,4,5],[3,4,5,6]]', + 'strict $[*] ? (@[*] < $x)' + PASSING x AS x, y AS y + COLUMNS ( + y text FORMAT JSON PATH '$', + NESTED PATH 'strict $[*] ? (@ >= $y)' + COLUMNS ( + z int PATH '$' + ) + ) + ) jt; + x | y | y | z +---+---+--------------+--- + 2 | 1 | [1, 2, 3] | 1 + 2 | 1 | [1, 2, 3] | 2 + 2 | 1 | [1, 2, 3] | 3 + 3 | 1 | [1, 2, 3] | 1 + 3 | 1 | [1, 2, 3] | 2 + 3 | 1 | [1, 2, 3] | 3 + 3 | 1 | [2, 3, 4, 5] | 2 + 3 | 1 | [2, 3, 4, 5] | 3 + 3 | 1 | [2, 3, 4, 5] | 4 + 3 | 1 | [2, 3, 4, 5] | 5 + 4 | 1 | [1, 2, 3] | 1 + 4 | 1 | [1, 2, 3] | 2 + 4 | 1 | [1, 2, 3] | 3 + 4 | 1 | [2, 3, 4, 5] | 2 + 4 | 1 | [2, 3, 4, 5] | 3 + 4 | 1 | [2, 3, 4, 5] | 4 + 4 | 1 | [2, 3, 4, 5] | 5 + 4 | 1 | [3, 4, 5, 6] | 3 + 4 | 1 | [3, 4, 5, 6] | 4 + 4 | 1 | [3, 4, 5, 6] | 5 + 4 | 1 | [3, 4, 5, 6] | 6 + 2 | 2 | [1, 2, 3] | 2 + 2 | 2 | [1, 2, 3] | 3 + 3 | 2 | [1, 2, 3] | 2 + 3 | 2 | [1, 2, 3] | 3 + 3 | 2 | [2, 3, 4, 5] | 2 + 3 | 2 | [2, 3, 4, 5] | 3 + 3 | 2 | [2, 3, 4, 5] | 4 + 3 | 2 | [2, 3, 4, 5] | 5 + 4 | 2 | [1, 2, 3] | 2 + 4 | 2 | [1, 2, 3] | 3 + 4 | 2 | [2, 3, 4, 5] | 2 + 4 | 2 | [2, 3, 4, 5] | 3 + 4 | 2 | [2, 3, 4, 5] | 4 + 4 | 2 | [2, 3, 4, 5] | 5 + 4 | 2 | [3, 4, 5, 6] | 3 + 4 | 2 | [3, 4, 5, 6] | 4 + 4 | 2 | [3, 4, 5, 6] | 5 + 4 | 2 | [3, 4, 5, 6] | 6 + 2 | 3 | [1, 2, 3] | 3 + 3 | 3 | [1, 2, 3] | 3 + 3 | 3 | [2, 3, 4, 5] | 3 + 3 | 3 | [2, 3, 4, 5] | 4 + 3 | 3 | [2, 3, 4, 5] | 5 + 4 | 3 | [1, 2, 3] | 3 + 4 | 3 | [2, 3, 4, 5] | 3 + 4 | 3 | [2, 3, 4, 5] | 4 + 4 | 3 | [2, 3, 4, 5] | 5 + 4 | 3 | [3, 4, 5, 6] | 3 + 4 | 3 | [3, 4, 5, 6] | 4 + 4 | 3 | [3, 4, 5, 6] | 5 + 4 | 3 | [3, 4, 5, 6] | 6 +(52 rows) + +-- Should fail (JSON arguments are not passed to column paths) +SELECT * +FROM JSON_TABLE( + jsonb '[1,2,3]', + '$[*] ? (@ < $x)' + PASSING 10 AS x + COLUMNS (y text FORMAT JSON PATH '$ ? (@ < $x)') + ) jt; +ERROR: could not find 'x' passed variable -- Extension: non-constant JSON path SELECT JSON_EXISTS(jsonb '{"a": 123}', '$' || '.' || 'a'); json_exists @@ -953,3 +1882,8 @@ SELECT JSON_QUERY(jsonb '{"a": 123}', '$' || '.' || 'a' WITH WRAPPER); SELECT JSON_QUERY(jsonb '{"a": 123}', 'error' || ' ' || 'error'); ERROR: bad jsonpath representation DETAIL: syntax error, unexpected IDENT_P at or near " " +-- Should fail (not supported) +SELECT * FROM JSON_TABLE(jsonb '{"a": 123}', '$' || '.' || 'a' COLUMNS (foo int)); +ERROR: only string constants supported in JSON_TABLE path specification +LINE 1: SELECT * FROM JSON_TABLE(jsonb '{"a": 123}', '$' || '.' || '... + ^ diff --git a/src/test/regress/sql/json_sqljson.sql b/src/test/regress/sql/json_sqljson.sql index 6146c45..896941c 100644 --- a/src/test/regress/sql/json_sqljson.sql +++ b/src/test/regress/sql/json_sqljson.sql @@ -302,6 +302,10 @@ INSERT INTO test_json_constraints VALUES ('{"a": 10}', 1); DROP TABLE test_json_constraints; +-- JSON_TABLE + +SELECT * FROM JSON_TABLE(NULL FORMAT JSON, '$' COLUMNS (foo text)); + -- Extension: non-constant JSON path SELECT JSON_EXISTS(json '{"a": 123}', '$' || '.' || 'a'); SELECT JSON_VALUE(json '{"a": 123}', '$' || '.' || 'a'); diff --git a/src/test/regress/sql/jsonb_sqljson.sql b/src/test/regress/sql/jsonb_sqljson.sql index a801bcf..3705e01 100644 --- a/src/test/regress/sql/jsonb_sqljson.sql +++ b/src/test/regress/sql/jsonb_sqljson.sql @@ -277,6 +277,568 @@ INSERT INTO test_jsonb_constraints VALUES ('{"a": 10}', 1); DROP TABLE test_jsonb_constraints; +-- JSON_TABLE + +-- Should fail (JSON_TABLE can be used only in FROM clause) +SELECT JSON_TABLE('[]', '$'); + +-- Should fail (no columns) +SELECT * FROM JSON_TABLE(NULL, '$' COLUMNS ()); + +-- NULL => empty table +SELECT * FROM JSON_TABLE(NULL::jsonb, '$' COLUMNS (foo int)) bar; + +-- +SELECT * FROM JSON_TABLE(jsonb '123', '$' + COLUMNS (item int PATH '$', foo int)) bar; + +-- JSON_TABLE: basic functionality +SELECT * +FROM + (VALUES + ('1'), + ('[]'), + ('{}'), + ('[1, 1.23, "2", "aaaaaaa", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""]') + ) vals(js) + LEFT OUTER JOIN +-- JSON_TABLE is implicitly lateral + JSON_TABLE( + vals.js::jsonb, 'lax $[*]' + COLUMNS ( + id FOR ORDINALITY, + id2 FOR ORDINALITY, -- allowed additional ordinality columns + "int" int PATH '$', + "text" text PATH '$', + "char(4)" char(4) PATH '$', + "bool" bool PATH '$', + "numeric" numeric PATH '$', + js json PATH '$', + jb jsonb PATH '$', + jst text FORMAT JSON PATH '$', + jsc char(4) FORMAT JSON PATH '$', + jsv varchar(4) FORMAT JSON PATH '$', + jsb jsonb FORMAT JSON PATH '$', + aaa int, -- implicit path '$."aaa"', + aaa1 int PATH '$.aaa' + ) + ) jt + ON true; + +-- JSON_TABLE: Test backward parsing + +CREATE VIEW jsonb_table_view AS +SELECT * FROM + JSON_TABLE( + jsonb 'null', 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c" + COLUMNS ( + id FOR ORDINALITY, + id2 FOR ORDINALITY, -- allowed additional ordinality columns + "int" int PATH '$', + "text" text PATH '$', + "char(4)" char(4) PATH '$', + "bool" bool PATH '$', + "numeric" numeric PATH '$', + js json PATH '$', + jb jsonb PATH '$', + jst text FORMAT JSON PATH '$', + jsc char(4) FORMAT JSON PATH '$', + jsv varchar(4) FORMAT JSON PATH '$', + jsb jsonb FORMAT JSON PATH '$', + aaa int, -- implicit path '$."aaa"', + aaa1 int PATH '$.aaa', + NESTED PATH '$[1]' AS p1 COLUMNS ( + a1 int, + NESTED PATH '$[*]' AS "p1 1" COLUMNS ( + a11 text + ), + b1 text + ), + NESTED PATH '$[2]' AS p2 COLUMNS ( + NESTED PATH '$[*]' AS "p2:1" COLUMNS ( + a21 text + ), + NESTED PATH '$[*]' AS p22 COLUMNS ( + a22 text + ) + ) + ) + ); + +\sv jsonb_table_view + +EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view; + +-- JSON_TABLE: ON EMPTY/ON ERROR behavior +SELECT * +FROM + (VALUES ('1'), ('"err"')) vals(js), + JSON_TABLE(vals.js::jsonb, '$' COLUMNS (a int PATH '$')) jt; + +SELECT * +FROM + (VALUES ('1'), ('"err"')) vals(js) + LEFT OUTER JOIN + JSON_TABLE(vals.js::jsonb, '$' COLUMNS (a int PATH '$') ERROR ON ERROR) jt + ON true; + +SELECT * +FROM + (VALUES ('1'), ('"err"')) vals(js) + LEFT OUTER JOIN + JSON_TABLE(vals.js::jsonb, '$' COLUMNS (a int PATH '$' ERROR ON ERROR)) jt + ON true; + +SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH '$.a' ERROR ON EMPTY)) jt; +SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH 'strict $.a' ERROR ON EMPTY) ERROR ON ERROR) jt; +SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH 'lax $.a' ERROR ON EMPTY) ERROR ON ERROR) jt; + +SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH '$' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt; +SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH 'strict $.a' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt; +SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH 'lax $.a' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt; + +-- JSON_TABLE: nested paths and plans + +-- Should fail (JSON_TABLE columns shall contain explicit AS path +-- specifications if explicit PLAN clause is used) +SELECT * FROM JSON_TABLE( + jsonb '[]', '$' -- AS required here + COLUMNS ( + foo int PATH '$' + ) + PLAN DEFAULT (UNION) +) jt; + +SELECT * FROM JSON_TABLE( + jsonb '[]', '$' AS path1 + COLUMNS ( + NESTED PATH '$' COLUMNS ( -- AS required here + foo int PATH '$' + ) + ) + PLAN DEFAULT (UNION) +) jt; + +-- Should fail (column names anf path names shall be distinct) +SELECT * FROM JSON_TABLE( + jsonb '[]', '$' AS a + COLUMNS ( + a int + ) +) jt; + +SELECT * FROM JSON_TABLE( + jsonb '[]', '$' AS a + COLUMNS ( + b int, + NESTED PATH '$' AS a + COLUMNS ( + c int + ) + ) +) jt; + +SELECT * FROM JSON_TABLE( + jsonb '[]', '$' + COLUMNS ( + b int, + NESTED PATH '$' AS b + COLUMNS ( + c int + ) + ) +) jt; + +SELECT * FROM JSON_TABLE( + jsonb '[]', '$' + COLUMNS ( + NESTED PATH '$' AS a + COLUMNS ( + b int + ), + NESTED PATH '$' + COLUMNS ( + NESTED PATH '$' AS a + COLUMNS ( + c int + ) + ) + ) +) jt; + +-- JSON_TABLE: plan validation + +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p1) +) jt; + +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0) +) jt; + +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 OUTER p3) +) jt; + +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 OUTER (p1 CROSS p13)) +) jt; + +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 OUTER (p1 CROSS p2)) +) jt; + +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 OUTER ((p1 UNION p11) CROSS p2)) +) jt; + +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 OUTER ((p1 INNER p11) CROSS p2)) +) jt; + +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 OUTER ((p1 INNER (p12 CROSS p11)) CROSS p2)) +) jt; + +SELECT * FROM JSON_TABLE( + jsonb 'null', 'strict $[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 OUTER ((p1 INNER (p12 CROSS p11)) CROSS (p2 INNER p21))) +) jt; + +SELECT * FROM JSON_TABLE( + jsonb 'null', 'strict $[*]' -- without root path name + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN ((p1 INNER (p12 CROSS p11)) CROSS (p2 INNER p21)) +) jt; + +-- JSON_TABLE: plan execution + +CREATE TEMP TABLE jsonb_table_test (js jsonb); + +INSERT INTO jsonb_table_test +VALUES ( + '[ + {"a": 1, "b": [], "c": []}, + {"a": 2, "b": [1, 2, 3], "c": [10, null, 20]}, + {"a": 3, "b": [1, 2], "c": []}, + {"x": "4", "b": [1, 2], "c": 123} + ]' +); + +-- unspecified plan (outer, union) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + ) jt; + +-- default plan (outer, union) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan default (outer, union) + ) jt; + +-- specific plan (p outer (pb union pc)) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan (p outer (pb union pc)) + ) jt; + +-- specific plan (p outer (pc union pb)) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan (p outer (pc union pb)) + ) jt; + +-- default plan (inner, union) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan default (inner) + ) jt; + +-- specific plan (p inner (pb union pc)) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan (p inner (pb union pc)) + ) jt; + +-- default plan (inner, cross) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan default (cross, inner) + ) jt; + +-- specific plan (p inner (pb cross pc)) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan (p inner (pb cross pc)) + ) jt; + +-- default plan (outer, cross) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan default (outer, cross) + ) jt; + +-- specific plan (p outer (pb cross pc)) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan (p outer (pb cross pc)) + ) jt; + + +select + jt.*, b1 + 100 as b +from + json_table (jsonb + '[ + {"a": 1, "b": [[1, 10], [2], [3, 30, 300]], "c": [1, null, 2]}, + {"a": 2, "b": [10, 20], "c": [1, null, 2]}, + {"x": "3", "b": [11, 22, 33, 44]} + ]', + '$[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on error, + nested path 'strict $.b[*]' as pb columns ( + b text format json path '$', + nested path 'strict $[*]' as pb1 columns ( + b1 int path '$' + ) + ), + nested path 'strict $.c[*]' as pc columns ( + c text format json path '$', + nested path 'strict $[*]' as pc1 columns ( + c1 int path '$' + ) + ) + ) + --plan default(outer, cross) + plan(p outer ((pb inner pb1) cross (pc outer pc1))) + ) jt; + +-- Should succeed (JSON arguments are passed to root and nested paths) +SELECT * +FROM + generate_series(1, 4) x, + generate_series(1, 3) y, + JSON_TABLE(jsonb + '[[1,2,3],[2,3,4,5],[3,4,5,6]]', + 'strict $[*] ? (@[*] < $x)' + PASSING x AS x, y AS y + COLUMNS ( + y text FORMAT JSON PATH '$', + NESTED PATH 'strict $[*] ? (@ >= $y)' + COLUMNS ( + z int PATH '$' + ) + ) + ) jt; + +-- Should fail (JSON arguments are not passed to column paths) +SELECT * +FROM JSON_TABLE( + jsonb '[1,2,3]', + '$[*] ? (@ < $x)' + PASSING 10 AS x + COLUMNS (y text FORMAT JSON PATH '$ ? (@ < $x)') + ) jt; + -- Extension: non-constant JSON path SELECT JSON_EXISTS(jsonb '{"a": 123}', '$' || '.' || 'a'); SELECT JSON_VALUE(jsonb '{"a": 123}', '$' || '.' || 'a'); @@ -285,3 +847,5 @@ SELECT JSON_QUERY(jsonb '{"a": 123}', '$' || '.' || 'a'); SELECT JSON_QUERY(jsonb '{"a": 123}', '$' || '.' || 'a' WITH WRAPPER); -- Should fail (invalid path) SELECT JSON_QUERY(jsonb '{"a": 123}', 'error' || ' ' || 'error'); +-- Should fail (not supported) +SELECT * FROM JSON_TABLE(jsonb '{"a": 123}', '$' || '.' || 'a' COLUMNS (foo int));