Add basic JSON_TABLE() functionality

JSON_TABLE() allows JSON data to be converted into a relational view
and thus used, for example, in a FROM clause, like other tabular
data.  Data to show in the view is selected from a source JSON object
using a JSON path expression to get a sequence of JSON objects that's
called a "row pattern", which becomes the source to compute the
SQL/JSON values that populate the view's output columns.  Column
values themselves are computed using JSON path expressions applied to
each of the JSON objects comprising the "row pattern", for which the
SQL/JSON query functions added in 6185c9737cf4 are used.

To implement JSON_TABLE() as a table function, this augments the
TableFunc and TableFuncScanState nodes that are currently used to
support XMLTABLE() with some JSON_TABLE()-specific fields.

Note that the JSON_TABLE() spec includes NESTED COLUMNS and PLAN
clauses, which are required to provide more flexibility to extract
data out of nested JSON objects, but they are not implemented here
to keep this commit of manageable size.

Author: Nikita Glukhov <n.gluhov@postgrespro.ru>
Author: Teodor Sigaev <teodor@sigaev.ru>
Author: Oleg Bartunov <obartunov@gmail.com>
Author: Alexander Korotkov <aekorotkov@gmail.com>
Author: Andrew Dunstan <andrew@dunslane.net>
Author: Amit Langote <amitlangote09@gmail.com>
Author: Jian He <jian.universality@gmail.com>

Reviewers have included (in no particular order):

Andres Freund, Alexander Korotkov, Pavel Stehule, Andrew Alsup,
Erik Rijkers, Zihong Yu, Himanshu Upadhyaya, Daniel Gustafsson,
Justin Pryzby, Álvaro Herrera, Jian He

Discussion: https://postgr.es/m/cd0bb935-0158-78a7-08b5-904886deac4b@postgrespro.ru
Discussion: https://postgr.es/m/20220616233130.rparivafipt6doj3@alap3.anarazel.de
Discussion: https://postgr.es/m/abd9b83b-aa66-f230-3d6d-734817f0995d%40postgresql.org
Discussion: https://postgr.es/m/CA+HiwqE4XTdfb1nW=Ojoy_tQSRhYt-q_kb6i5d4xcKyrLC1Nbg@mail.gmail.com

8 files changed, 643 insertions, 27 deletions

diff --git a/src/backend/parser/Makefile b/src/backend/parser/Makefile
index 401c16686c6..3162a01f302 100644
--- a/src/backend/parser/Makefile
+++ b/src/backend/parser/Makefile
@@ -23,6 +23,7 @@ OBJS = \
 	parse_enr.o \
 	parse_expr.o \
 	parse_func.o \
+	parse_jsontable.o \
 	parse_merge.o \
 	parse_node.o \
 	parse_oper.o \
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index f1af6147c37..6ea68722e3d 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -170,7 +170,6 @@ static void updateRawStmtEnd(RawStmt *rs, int end_location);
 static Node *makeColumnRef(char *colname, List *indirection,
 						   int location, core_yyscan_t yyscanner);
 static Node *makeTypeCast(Node *arg, TypeName *typename, int location);
-static Node *makeStringConst(char *str, int location);
 static Node *makeStringConstCast(char *str, int location, TypeName *typename);
 static Node *makeIntConst(int val, int location);
 static Node *makeFloatConst(char *str, int location);
@@ -659,12 +658,17 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 				json_argument
 				json_behavior
 				json_on_error_clause_opt
+				json_table
+				json_table_column_definition
+				json_table_column_path_clause_opt
 %type <list>	json_name_and_value_list
 				json_value_expr_list
 				json_array_aggregate_order_by_clause_opt
 				json_arguments
 				json_behavior_clause_opt
 				json_passing_clause_opt
+				json_table_column_definition_list
+%type <str>		json_table_path_name_opt
 %type <ival>	json_behavior_type
 				json_predicate_type_constraint
 				json_quotes_clause_opt
@@ -737,7 +741,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_SCALAR JSON_SERIALIZE JSON_VALUE
+	JSON_QUERY JSON_SCALAR JSON_SERIALIZE JSON_TABLE JSON_VALUE
 
 	KEEP KEY KEYS
 
@@ -748,8 +752,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 	MAPPING MATCH MATCHED MATERIALIZED MAXVALUE MERGE MERGE_ACTION METHOD
 	MINUTE_P MINVALUE MODE MONTH_P MOVE
 
-	NAME_P NAMES NATIONAL NATURAL NCHAR NEW NEXT NFC NFD NFKC NFKD NO NONE
-	NORMALIZE NORMALIZED
+	NAME_P NAMES NATIONAL NATURAL NCHAR NEW NEXT NFC NFD NFKC NFKD NO
+	NONE NORMALIZE NORMALIZED
 	NOT NOTHING NOTIFY NOTNULL NOWAIT NULL_P NULLIF
 	NULLS_P NUMERIC
 
@@ -757,8 +761,9 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 	ORDER ORDINALITY OTHERS OUT_P OUTER_P
 	OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER
 
-	PARALLEL PARAMETER PARSER PARTIAL PARTITION PASSING PASSWORD
-	PERIOD PLACING PLANS POLICY
+	PARALLEL PARAMETER PARSER PARTIAL PARTITION PASSING PASSWORD PATH
+	PERIOD PLACING PLAN PLANS POLICY
+
 	POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY
 	PRIOR PRIVILEGES PROCEDURAL PROCEDURE PROCEDURES PROGRAM PUBLICATION
 
@@ -877,9 +882,9 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
  * json_predicate_type_constraint and json_key_uniqueness_constraint_opt
  * productions (see comments there).
  */
-%nonassoc	UNBOUNDED		/* ideally would have same precedence as IDENT */
+%nonassoc	UNBOUNDED /* ideally would have same precedence as IDENT */
 %nonassoc	IDENT PARTITION RANGE ROWS GROUPS PRECEDING FOLLOWING CUBE ROLLUP
-			SET KEYS OBJECT_P SCALAR VALUE_P WITH WITHOUT
+			SET KEYS OBJECT_P SCALAR VALUE_P WITH WITHOUT PATH
 %left		Op OPERATOR		/* multi-character ops and user-defined operators */
 %left		'+' '-'
 %left		'*' '/' '%'
@@ -13493,6 +13498,21 @@ 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;
+				}
 		;
 
 
@@ -14060,6 +14080,8 @@ xmltable_column_option_el:
 				{ $$ = makeDefElem("is_not_null", (Node *) makeBoolean(true), @1); }
 			| NULL_P
 				{ $$ = makeDefElem("is_not_null", (Node *) makeBoolean(false), @1); }
+			| PATH b_expr
+				{ $$ = makeDefElem("path", $2, @1); }
 		;
 
 xml_namespace_list:
@@ -14088,6 +14110,123 @@ xml_namespace_el:
 				}
 		;
 
+json_table:
+			JSON_TABLE '('
+				json_value_expr ',' a_expr json_table_path_name_opt
+				json_passing_clause_opt
+				COLUMNS '(' json_table_column_definition_list ')'
+				json_on_error_clause_opt
+			')'
+				{
+					JsonTable *n = makeNode(JsonTable);
+					char	  *pathstring;
+
+					n->context_item = (JsonValueExpr *) $3;
+					if (!IsA($5, A_Const) ||
+						castNode(A_Const, $5)->val.node.type != T_String)
+						ereport(ERROR,
+								errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+								errmsg("only string constants are supported in JSON_TABLE path specification"),
+								parser_errposition(@5));
+					pathstring = castNode(A_Const, $5)->val.sval.sval;
+					n->pathspec = makeJsonTablePathSpec(pathstring, $6, @5, @6);
+					n->passing = $7;
+					n->columns = $10;
+					n->on_error = (JsonBehavior *) $12;
+					n->location = @1;
+					$$ = (Node *) n;
+				}
+		;
+
+json_table_path_name_opt:
+			AS name			{ $$ = $2; }
+			| /* empty */	{ $$ = NULL; }
+		;
+
+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:
+			ColId FOR ORDINALITY
+				{
+					JsonTableColumn *n = makeNode(JsonTableColumn);
+
+					n->coltype = JTC_FOR_ORDINALITY;
+					n->name = $1;
+					n->location = @1;
+					$$ = (Node *) n;
+				}
+			| ColId Typename
+				json_table_column_path_clause_opt
+				json_wrapper_behavior
+				json_quotes_clause_opt
+				json_behavior_clause_opt
+				{
+					JsonTableColumn *n = makeNode(JsonTableColumn);
+
+					n->coltype = JTC_REGULAR;
+					n->name = $1;
+					n->typeName = $2;
+					n->format = makeJsonFormat(JS_FORMAT_DEFAULT, JS_ENC_DEFAULT, -1);
+					n->pathspec = (JsonTablePathSpec *) $3;
+					n->wrapper = $4;
+					n->quotes = $5;
+					n->on_empty = (JsonBehavior *) linitial($6);
+					n->on_error = (JsonBehavior *) lsecond($6);
+					n->location = @1;
+					$$ = (Node *) n;
+				}
+			| ColId Typename json_format_clause
+				json_table_column_path_clause_opt
+				json_wrapper_behavior
+				json_quotes_clause_opt
+				json_behavior_clause_opt
+				{
+					JsonTableColumn *n = makeNode(JsonTableColumn);
+
+					n->coltype = JTC_FORMATTED;
+					n->name = $1;
+					n->typeName = $2;
+					n->format = (JsonFormat *) $3;
+					n->pathspec = (JsonTablePathSpec *) $4;
+					n->wrapper = $5;
+					n->quotes = $6;
+					n->on_empty = (JsonBehavior *) linitial($7);
+					n->on_error = (JsonBehavior *) lsecond($7);
+					n->location = @1;
+					$$ = (Node *) n;
+				}
+			| ColId Typename
+				EXISTS json_table_column_path_clause_opt
+				json_behavior_clause_opt
+				{
+					JsonTableColumn *n = makeNode(JsonTableColumn);
+
+					n->coltype = JTC_EXISTS;
+					n->name = $1;
+					n->typeName = $2;
+					n->format = makeJsonFormat(JS_FORMAT_DEFAULT, JS_ENC_DEFAULT, -1);
+					n->wrapper = JSW_NONE;
+					n->quotes = JS_QUOTES_UNSPEC;
+					n->pathspec = (JsonTablePathSpec *) $4;
+					n->on_empty = (JsonBehavior *) linitial($5);
+					n->on_error = (JsonBehavior *) lsecond($5);
+					n->location = @1;
+					$$ = (Node *) n;
+				}
+		;
+
+json_table_column_path_clause_opt:
+			PATH Sconst
+				{ $$ = (Node *) makeJsonTablePathSpec($2, NULL, @2, -1); }
+			| /* EMPTY */
+				{ $$ = NULL; }
+		;
+
 /*****************************************************************************
  *
  *	Type syntax
@@ -17531,7 +17670,9 @@ unreserved_keyword:
 			| PARTITION
 			| PASSING
 			| PASSWORD
+			| PATH
 			| PERIOD
+			| PLAN
 			| PLANS
 			| POLICY
 			| PRECEDING
@@ -17698,6 +17839,7 @@ col_name_keyword:
 			| JSON_QUERY
 			| JSON_SCALAR
 			| JSON_SERIALIZE
+			| JSON_TABLE
 			| JSON_VALUE
 			| LEAST
 			| MERGE_ACTION
@@ -18067,6 +18209,7 @@ bare_label_keyword:
 			| JSON_QUERY
 			| JSON_SCALAR
 			| JSON_SERIALIZE
+			| JSON_TABLE
 			| JSON_VALUE
 			| KEEP
 			| KEY
@@ -18151,8 +18294,10 @@ bare_label_keyword:
 			| PARTITION
 			| PASSING
 			| PASSWORD
+			| PATH
 			| PERIOD
 			| PLACING
+			| PLAN
 			| PLANS
 			| POLICY
 			| POSITION
@@ -18423,18 +18568,6 @@ makeTypeCast(Node *arg, TypeName *typename, int location)
 }
 
 static Node *
-makeStringConst(char *str, int location)
-{
-	A_Const	   *n = makeNode(A_Const);
-
-	n->val.sval.type = T_String;
-	n->val.sval.sval = str;
-	n->location = location;
-
-   return (Node *) n;
-}
-
-static Node *
 makeStringConstCast(char *str, int location, TypeName *typename)
 {
 	Node	   *s = makeStringConst(str, location);
diff --git a/src/backend/parser/meson.build b/src/backend/parser/meson.build
index 8e8295640bc..573d70b3d1b 100644
--- a/src/backend/parser/meson.build
+++ b/src/backend/parser/meson.build
@@ -10,6 +10,7 @@ backend_sources += files(
   'parse_enr.c',
   'parse_expr.c',
   'parse_func.c',
+  'parse_jsontable.c',
   'parse_merge.c',
   'parse_node.c',
   'parse_oper.c',
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index d2ac86777c2..4fc5fc87e07 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -695,7 +695,11 @@ transformRangeTableFunc(ParseState *pstate, RangeTableFunc *rtf)
 	char	  **names;
 	int			colno;
 
-	/* Currently only XMLTABLE is supported */
+	/*
+	 * Currently we only support XMLTABLE here.  See transformJsonTable() for
+	 * JSON_TABLE support.
+	 */
+	tf->functype = TFT_XMLTABLE;
 	constructName = "XMLTABLE";
 	docType = XMLOID;
 
@@ -1102,13 +1106,17 @@ transformFromClauseItem(ParseState *pstate, Node *n,
 		rtr->rtindex = nsitem->p_rtindex;
 		return (Node *) rtr;
 	}
-	else if (IsA(n, RangeTableFunc))
+	else if (IsA(n, RangeTableFunc) || IsA(n, JsonTable))
 	{
 		/* table function is like a plain relation */
 		RangeTblRef *rtr;
 		ParseNamespaceItem *nsitem;
 
-		nsitem = transformRangeTableFunc(pstate, (RangeTableFunc *) n);
+		if (IsA(n, JsonTable))
+			nsitem = transformJsonTable(pstate, (JsonTable *) n);
+		else
+			nsitem = transformRangeTableFunc(pstate, (RangeTableFunc *) n);
+
 		*top_nsitem = nsitem;
 		*namespace = list_make1(nsitem);
 		rtr = makeNode(RangeTblRef);
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 73c83cea4ac..81e66020858 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -4245,7 +4245,8 @@ transformJsonSerializeExpr(ParseState *pstate, JsonSerializeExpr *expr)
 }
 
 /*
- * Transform JSON_VALUE, JSON_QUERY, JSON_EXISTS functions into a JsonExpr node.
+ * Transform JSON_VALUE, JSON_QUERY, JSON_EXISTS, JSON_TABLE functions into
+ * a JsonExpr node.
  */
 static Node *
 transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
@@ -4269,6 +4270,9 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
 			func_name = "JSON_VALUE";
 			default_format = JS_FORMAT_DEFAULT;
 			break;
+		case JSON_TABLE_OP:
+			func_name = "JSON_TABLE";
+			break;
 		default:
 			elog(ERROR, "invalid JsonFuncExpr op %d", (int) func->op);
 			break;
@@ -4350,6 +4354,42 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
 				jsexpr->returning->typmod = -1;
 			}
 
+			/* JSON_TABLE() COLUMNS can specify a non-boolean type. */
+			if (jsexpr->returning->typid != BOOLOID)
+			{
+				Node	   *coercion_expr;
+				CaseTestExpr *placeholder = makeNode(CaseTestExpr);
+				int			location = exprLocation((Node *) jsexpr);
+
+				/*
+				 * We abuse CaseTestExpr here as placeholder to pass the
+				 * result of evaluating JSON_EXISTS to the coercion
+				 * expression.
+				 */
+				placeholder->typeId = BOOLOID;
+				placeholder->typeMod = -1;
+				placeholder->collation = InvalidOid;
+
+				coercion_expr =
+					coerce_to_target_type(pstate, (Node *) placeholder, BOOLOID,
+										  jsexpr->returning->typid,
+										  jsexpr->returning->typmod,
+										  COERCION_EXPLICIT,
+										  COERCE_IMPLICIT_CAST,
+										  location);
+
+				if (coercion_expr == NULL)
+					ereport(ERROR,
+							(errcode(ERRCODE_CANNOT_COERCE),
+							 errmsg("cannot cast type %s to %s",
+									format_type_be(BOOLOID),
+									format_type_be(jsexpr->returning->typid)),
+							 parser_coercion_errposition(pstate, location, (Node *) jsexpr)));
+
+				if (coercion_expr != (Node *) placeholder)
+					jsexpr->coercion_expr = coercion_expr;
+			}
+
 			jsexpr->on_error = transformJsonBehavior(pstate, func->on_error,
 													 JSON_BEHAVIOR_FALSE,
 													 jsexpr->returning);
@@ -4414,6 +4454,17 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
 													 jsexpr->returning);
 			break;
 
+		case JSON_TABLE_OP:
+			if (!OidIsValid(jsexpr->returning->typid))
+			{
+				jsexpr->returning->typid = exprType(jsexpr->formatted_expr);
+				jsexpr->returning->typmod = -1;
+			}
+			jsexpr->on_error = transformJsonBehavior(pstate, func->on_error,
+													 JSON_BEHAVIOR_EMPTY,
+													 jsexpr->returning);
+			break;
+
 		default:
 			elog(ERROR, "invalid JsonFuncExpr op %d", (int) func->op);
 			break;
diff --git a/src/backend/parser/parse_jsontable.c b/src/backend/parser/parse_jsontable.c
new file mode 100644
index 00000000000..060f62170e8
--- /dev/null
+++ b/src/backend/parser/parse_jsontable.c
@@ -0,0 +1,421 @@
+/*-------------------------------------------------------------------------
+ *
+ * parse_jsontable.c
+ *	  parsing of JSON_TABLE
+ *
+ * Portions Copyright (c) 1996-2024, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ *
+ * IDENTIFICATION
+ *	  src/backend/parser/parse_jsontable.c
+ *
+ *-------------------------------------------------------------------------
+ */
+
+#include "postgres.h"
+
+#include "catalog/pg_collation.h"
+#include "catalog/pg_type.h"
+#include "miscadmin.h"
+#include "nodes/makefuncs.h"
+#include "nodes/nodeFuncs.h"
+#include "optimizer/optimizer.h"
+#include "parser/parse_clause.h"
+#include "parser/parse_collate.h"
+#include "parser/parse_expr.h"
+#include "parser/parse_relation.h"
+#include "parser/parse_type.h"
+#include "utils/builtins.h"
+#include "utils/json.h"
+#include "utils/lsyscache.h"
+
+/* Context for transformJsonTableColumns() */
+typedef struct JsonTableParseContext
+{
+	ParseState *pstate;
+	JsonTable  *jt;
+	TableFunc  *tf;
+	List	   *pathNames;		/* list of all path and columns names */
+	int			pathNameId;		/* path name id counter */
+} JsonTableParseContext;
+
+static JsonTablePlan *transformJsonTableColumns(JsonTableParseContext *cxt,
+												List *columns,
+												List *passingArgs,
+												JsonTablePathSpec *pathspec);
+static JsonFuncExpr *transformJsonTableColumn(JsonTableColumn *jtc,
+											  Node *contextItemExpr,
+											  List *passingArgs);
+static bool isCompositeType(Oid typid);
+static JsonTablePlan *makeJsonTablePathScan(JsonTablePathSpec *pathspec,
+											bool errorOnError);
+static void CheckDuplicateColumnOrPathNames(JsonTableParseContext *cxt,
+											List *columns);
+static bool LookupPathOrColumnName(JsonTableParseContext *cxt, char *name);
+static char *generateJsonTablePathName(JsonTableParseContext *cxt);
+
+/*
+ * transformJsonTable -
+ *			Transform a raw JsonTable into TableFunc
+ *
+ * Mainly, this transforms the JSON_TABLE() document-generating expression
+ * (jt->context_item) and the column-generating expressions (jt->columns) to
+ * populate TableFunc.docexpr and TableFunc.colvalexprs, respectively. Also,
+ * the PASSING values (jt->passing) are transformed and added into
+ * TableFunc.passvalexprs.
+ */
+ParseNamespaceItem *
+transformJsonTable(ParseState *pstate, JsonTable *jt)
+{
+	TableFunc  *tf;
+	JsonFuncExpr *jfe;
+	JsonExpr   *je;
+	JsonTablePathSpec *rootPathSpec = jt->pathspec;
+	bool		is_lateral;
+	JsonTableParseContext cxt = {pstate};
+
+	Assert(IsA(rootPathSpec->string, A_Const) &&
+		   castNode(A_Const, rootPathSpec->string)->val.node.type == T_String);
+
+	if (jt->on_error &&
+		jt->on_error->btype != JSON_BEHAVIOR_ERROR &&
+		jt->on_error->btype != JSON_BEHAVIOR_EMPTY &&
+		jt->on_error->btype != JSON_BEHAVIOR_EMPTY_ARRAY)
+		ereport(ERROR,
+				errcode(ERRCODE_SYNTAX_ERROR),
+				errmsg("invalid ON ERROR behavior"),
+				errdetail("Only EMPTY or ERROR is allowed in the top-level ON ERROR clause."),
+				parser_errposition(pstate, jt->on_error->location));
+
+	cxt.pathNameId = 0;
+	if (rootPathSpec->name == NULL)
+		rootPathSpec->name = generateJsonTablePathName(&cxt);
+	cxt.pathNames = list_make1(rootPathSpec->name);
+	CheckDuplicateColumnOrPathNames(&cxt, jt->columns);
+
+	/*
+	 * 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 = makeNode(TableFunc);
+	tf->functype = TFT_JSON_TABLE;
+
+	/*
+	 * Transform JsonFuncExpr representing the top JSON_TABLE context_item and
+	 * pathspec into a dummy JSON_TABLE_OP JsonExpr.
+	 */
+	jfe = makeNode(JsonFuncExpr);
+	jfe->op = JSON_TABLE_OP;
+	jfe->context_item = jt->context_item;
+	jfe->pathspec = (Node *) rootPathSpec->string;
+	jfe->passing = jt->passing;
+	jfe->on_empty = NULL;
+	jfe->on_error = jt->on_error;
+	jfe->location = jt->location;
+	tf->docexpr = transformExpr(pstate, (Node *) jfe, EXPR_KIND_FROM_FUNCTION);
+
+	/*
+	 * Create a JsonTablePlan that will generate row pattern that becomes
+	 * source data for JSON path expressions in jt->columns.  This also adds
+	 * the columns' transformed JsonExpr nodes into tf->colvalexprs.
+	 */
+	cxt.jt = jt;
+	cxt.tf = tf;
+	tf->plan = (Node *) transformJsonTableColumns(&cxt, jt->columns,
+												  jt->passing,
+												  rootPathSpec);
+
+	/*
+	 * Copy the transformed PASSING arguments into the TableFunc node, because
+	 * they are evaluated separately from the JsonExpr that we just put in
+	 * TableFunc.docexpr.  JsonExpr.passing_values is still kept around for
+	 * get_json_table().
+	 */
+	je = (JsonExpr *) tf->docexpr;
+	tf->passingvalexprs = copyObject(je->passing_values);
+
+	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);
+}
+
+/*
+ * Check if a column / path name is duplicated in the given shared list of
+ * names.
+ */
+static void
+CheckDuplicateColumnOrPathNames(JsonTableParseContext *cxt,
+								List *columns)
+{
+	ListCell   *lc1;
+
+	foreach(lc1, columns)
+	{
+		JsonTableColumn *jtc = castNode(JsonTableColumn, lfirst(lc1));
+
+		if (LookupPathOrColumnName(cxt, jtc->name))
+			ereport(ERROR,
+					errcode(ERRCODE_DUPLICATE_ALIAS),
+					errmsg("duplicate JSON_TABLE column or path name: %s",
+						   jtc->name),
+					parser_errposition(cxt->pstate, jtc->location));
+		cxt->pathNames = lappend(cxt->pathNames, jtc->name);
+	}
+}
+
+/*
+ * Lookup a column/path name in the given name list, returning true if already
+ * there.
+ */
+static bool
+LookupPathOrColumnName(JsonTableParseContext *cxt, char *name)
+{
+	ListCell   *lc;
+
+	foreach(lc, cxt->pathNames)
+	{
+		if (strcmp(name, (const char *) lfirst(lc)) == 0)
+			return true;
+	}
+
+	return false;
+}
+
+/* Generate a new unique JSON_TABLE path name. */
+static char *
+generateJsonTablePathName(JsonTableParseContext *cxt)
+{
+	char		namebuf[32];
+	char	   *name = namebuf;
+
+	snprintf(namebuf, sizeof(namebuf), "json_table_path_%d",
+			 cxt->pathNameId++);
+
+	name = pstrdup(name);
+	cxt->pathNames = lappend(cxt->pathNames, name);
+
+	return name;
+}
+
+/*
+ * Create a JsonTablePlan that will supply the source row for 'columns'
+ * using 'pathspec' and append the columns' transformed JsonExpr nodes and
+ * their type/collation information to cxt->tf.
+ */
+static JsonTablePlan *
+transformJsonTableColumns(JsonTableParseContext *cxt, List *columns,
+						  List *passingArgs,
+						  JsonTablePathSpec *pathspec)
+{
+	ParseState *pstate = cxt->pstate;
+	JsonTable  *jt = cxt->jt;
+	TableFunc  *tf = cxt->tf;
+	ListCell   *col;
+	bool		ordinality_found = false;
+	bool		errorOnError = jt->on_error &&
+		jt->on_error->btype == JSON_BEHAVIOR_ERROR;
+	Oid			contextItemTypid = exprType(tf->docexpr);
+
+	foreach(col, columns)
+	{
+		JsonTableColumn *rawc = castNode(JsonTableColumn, lfirst(col));
+		Oid			typid;
+		int32		typmod;
+		Oid			typcoll = InvalidOid;
+		Node	   *colexpr;
+
+		Assert(rawc->name);
+		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:
+				if (ordinality_found)
+					ereport(ERROR,
+							(errcode(ERRCODE_SYNTAX_ERROR),
+							 errmsg("cannot use more than one FOR ORDINALITY column"),
+							 parser_errposition(pstate, rawc->location)));
+				ordinality_found = true;
+				colexpr = NULL;
+				typid = INT4OID;
+				typmod = -1;
+				break;
+
+			case JTC_REGULAR:
+				typenameTypeIdAndMod(pstate, rawc->typeName, &typid, &typmod);
+
+				/*
+				 * Use JTC_FORMATTED so as to use JSON_QUERY for this column
+				 * if the specified type is one that's better handled using
+				 * JSON_QUERY() or if non-default WRAPPER or QUOTES behavior
+				 * is specified.
+				 */
+				if (isCompositeType(typid) ||
+					rawc->quotes != JS_QUOTES_UNSPEC ||
+					rawc->wrapper != JSW_UNSPEC)
+					rawc->coltype = JTC_FORMATTED;
+
+				/* FALLTHROUGH */
+			case JTC_FORMATTED:
+			case JTC_EXISTS:
+				{
+					JsonFuncExpr *jfe;
+					CaseTestExpr *param = makeNode(CaseTestExpr);
+
+					param->collation = InvalidOid;
+					param->typeId = contextItemTypid;
+					param->typeMod = -1;
+
+					jfe = transformJsonTableColumn(rawc, (Node *) param,
+												   passingArgs);
+
+					colexpr = transformExpr(pstate, (Node *) jfe,
+											EXPR_KIND_FROM_FUNCTION);
+					assign_expr_collations(pstate, colexpr);
+
+					typid = exprType(colexpr);
+					typmod = exprTypmod(colexpr);
+					typcoll = exprCollation(colexpr);
+					break;
+				}
+
+			default:
+				elog(ERROR, "unknown JSON_TABLE column type: %d", (int) rawc->coltype);
+				break;
+		}
+
+		tf->coltypes = lappend_oid(tf->coltypes, typid);
+		tf->coltypmods = lappend_int(tf->coltypmods, typmod);
+		tf->colcollations = lappend_oid(tf->colcollations, typcoll);
+		tf->colvalexprs = lappend(tf->colvalexprs, colexpr);
+	}
+
+	return makeJsonTablePathScan(pathspec, errorOnError);
+}
+
+/*
+ * Check if the type is "composite" for the purpose of checking whether to use
+ * JSON_VALUE() or JSON_QUERY() for a given JsonTableColumn.
+ */
+static bool
+isCompositeType(Oid typid)
+{
+	char		typtype = get_typtype(typid);
+
+	return typid == JSONOID ||
+		typid == JSONBOID ||
+		typid == RECORDOID ||
+		type_is_array(typid) ||
+		typtype == TYPTYPE_COMPOSITE ||
+	/* domain over one of the above? */
+		(typtype == TYPTYPE_DOMAIN &&
+		 isCompositeType(getBaseType(typid)));
+}
+
+/*
+ * Transform JSON_TABLE column definition into a JsonFuncExpr
+ * This turns:
+ *   - regular column into JSON_VALUE()
+ *   - FORMAT JSON column into JSON_QUERY()
+ *   - EXISTS column into JSON_EXISTS()
+ */
+static JsonFuncExpr *
+transformJsonTableColumn(JsonTableColumn *jtc, Node *contextItemExpr,
+						 List *passingArgs)
+{
+	Node	   *pathspec;
+	JsonFuncExpr *jfexpr = makeNode(JsonFuncExpr);
+
+	/*
+	 * XXX consider inventing JSON_TABLE_VALUE_OP, etc. and pass the column
+	 * name via JsonExpr so that JsonPathValue(), etc. can provide error
+	 * message tailored to JSON_TABLE(), such as by mentioning the column
+	 * names in the message.
+	 */
+	if (jtc->coltype == JTC_REGULAR)
+		jfexpr->op = JSON_VALUE_OP;
+	else if (jtc->coltype == JTC_EXISTS)
+		jfexpr->op = JSON_EXISTS_OP;
+	else
+		jfexpr->op = JSON_QUERY_OP;
+
+	jfexpr->context_item = makeJsonValueExpr((Expr *) contextItemExpr, NULL,
+											 makeJsonFormat(JS_FORMAT_DEFAULT,
+															JS_ENC_DEFAULT,
+															-1));
+	if (jtc->pathspec)
+		pathspec = (Node *) jtc->pathspec->string;
+	else
+	{
+		/* Construct default path as '$."column_name"' */
+		StringInfoData path;
+
+		initStringInfo(&path);
+
+		appendStringInfoString(&path, "$.");
+		escape_json(&path, jtc->name);
+
+		pathspec = makeStringConst(path.data, -1);
+	}
+	jfexpr->pathspec = pathspec;
+	jfexpr->passing = passingArgs;
+	jfexpr->output = makeNode(JsonOutput);
+	jfexpr->output->typeName = jtc->typeName;
+	jfexpr->output->returning = makeNode(JsonReturning);
+	jfexpr->output->returning->format = jtc->format;
+	jfexpr->on_empty = jtc->on_empty;
+	jfexpr->on_error = jtc->on_error;
+	jfexpr->quotes = jtc->quotes;
+	jfexpr->wrapper = jtc->wrapper;
+	jfexpr->location = jtc->location;
+
+	return jfexpr;
+}
+
+/*
+ * Create a JsonTablePlan for given path and ON ERROR behavior.
+ */
+static JsonTablePlan *
+makeJsonTablePathScan(JsonTablePathSpec *pathspec, bool errorOnError)
+{
+	JsonTablePathScan *scan = makeNode(JsonTablePathScan);
+	char	   *pathstring;
+	Const	   *value;
+
+	Assert(IsA(pathspec->string, A_Const));
+	pathstring = castNode(A_Const, pathspec->string)->val.sval.sval;
+	value = makeConst(JSONPATHOID, -1, InvalidOid, -1,
+					  DirectFunctionCall1(jsonpath_in,
+										  CStringGetDatum(pathstring)),
+					  false, false);
+
+	scan->plan.type = T_JsonTablePathScan;
+	scan->path = makeJsonTablePath(value, pathspec->name);
+	scan->errorOnError = errorOnError;
+
+	return (JsonTablePlan *) scan;
+}
diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c
index 427b7325db8..7ca793a369f 100644
--- a/src/backend/parser/parse_relation.c
+++ b/src/backend/parser/parse_relation.c
@@ -2071,8 +2071,6 @@ addRangeTableEntryForTableFunc(ParseState *pstate,
 	Assert(list_length(tf->coltypmods) == list_length(tf->colnames));
 	Assert(list_length(tf->colcollations) == list_length(tf->colnames));
 
-	refname = alias ? alias->aliasname : pstrdup("xmltable");
-
 	rte->rtekind = RTE_TABLEFUNC;
 	rte->relid = InvalidOid;
 	rte->subquery = NULL;
@@ -2082,6 +2080,8 @@ addRangeTableEntryForTableFunc(ParseState *pstate,
 	rte->colcollations = tf->colcollations;
 	rte->alias = alias;
 
+	refname = alias ? alias->aliasname :
+		pstrdup(tf->functype == TFT_XMLTABLE ? "xmltable" : "json_table");
 	eref = alias ? copyObject(alias) : makeAlias(refname, NIL);
 	numaliases = list_length(eref->colnames);
 
@@ -2094,7 +2094,7 @@ addRangeTableEntryForTableFunc(ParseState *pstate,
 		ereport(ERROR,
 				(errcode(ERRCODE_INVALID_COLUMN_REFERENCE),
 				 errmsg("%s function has %d columns available but %d columns specified",
-						"XMLTABLE",
+						tf->functype == TFT_XMLTABLE ? "XMLTABLE" : "JSON_TABLE",
 						list_length(tf->colnames), numaliases)));
 
 	rte->eref = eref;
diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c
index 1276f336041..ee6fcd0503a 100644
--- a/src/backend/parser/parse_target.c
+++ b/src/backend/parser/parse_target.c
@@ -2019,6 +2019,7 @@ FigureColnameInternal(Node *node, char **name)
 				case JSON_VALUE_OP:
 					*name = "json_value";
 					return 2;
+					/* JSON_TABLE_OP can't happen here. */
 				default:
 					elog(ERROR, "unrecognized JsonExpr op: %d",
 						 (int) ((JsonFuncExpr *) node)->op);