JSON_TABLE

This feature allows jsonb data to be treated as a table and thus used in
a FROM clause like other tabular data. Data can be selected from the
jsonb using jsonpath expressions, and hoisted out of nested structures
in the jsonb to form multiple rows, more or less like an outer join.

Nikita Glukhov

Reviewers have included (in no particular order) Andres Freund, Alexander
Korotkov, Pavel Stehule, Andrew Alsup, Erik Rijkers, Zhihong Yu (whose
name I previously misspelled), Himanshu Upadhyaya, Daniel Gustafsson,
Justin Pryzby.

Discussion: https://postgr.es/m/7e2cb85d-24cf-4abb-30a5-1a33715959bd@postgrespro.ru

7 files changed, 103 insertions, 2 deletions

diff --git a/src/include/executor/execExpr.h b/src/include/executor/execExpr.h
index 9ce8df17e5d..9df70e6f06f 100644
--- a/src/include/executor/execExpr.h
+++ b/src/include/executor/execExpr.h
@@ -850,6 +850,10 @@ extern Datum ExecPrepareJsonItemCoercion(struct JsonbValue *item,
 										 struct JsonCoercionState **pjcstate);
 extern bool ExecEvalJsonNeedsSubTransaction(JsonExpr *jsexpr,
 											struct JsonCoercionsState *);
+extern Datum ExecEvalExprPassingCaseValue(ExprState *estate,
+										  ExprContext *econtext, bool *isnull,
+										  Datum caseval_datum,
+										  bool caseval_isnull);
 
 extern void ExecAggInitGroup(AggState *aggstate, AggStatePerTrans pertrans, AggStatePerGroup pergroup,
 							 ExprContext *aggcontext);
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index 53f6b05a3f2..aefce33e284 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -212,6 +212,8 @@ typedef enum NodeTag
 	T_JsonExpr,
 	T_JsonCoercion,
 	T_JsonItemCoercions,
+	T_JsonTableParent,
+	T_JsonTableSibling,
 
 	/*
 	 * TAGS FOR EXPRESSION STATE NODES (execnodes.h)
@@ -514,6 +516,8 @@ typedef enum NodeTag
 	T_JsonArrayAgg,
 	T_JsonFuncExpr,
 	T_JsonIsPredicate,
+	T_JsonTable,
+	T_JsonTableColumn,
 	T_JsonCommon,
 	T_JsonArgument,
 	T_JsonKeyValue,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 8a9ccf62210..e58211eac1b 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1606,6 +1606,19 @@ typedef enum JsonQuotes
 } JsonQuotes;
 
 /*
+ * JsonTableColumnType -
+ *		enumeration of JSON_TABLE column types
+ */
+typedef enum
+{
+	JTC_FOR_ORDINALITY,
+	JTC_REGULAR,
+	JTC_EXISTS,
+	JTC_FORMATTED,
+	JTC_NESTED,
+} JsonTableColumnType;
+
+/*
  * JsonPathSpec -
  *		representation of JSON path constant
  */
@@ -1665,6 +1678,41 @@ 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 */
+	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;
+
+/*
+ * JsonTable -
+ *		untransformed representation of JSON_TABLE
+ */
+typedef struct JsonTable
+{
+	NodeTag		type;
+	JsonCommon *common;					/* common JSON path syntax fields */
+	List	   *columns;				/* list of JsonTableColumn */
+	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;
+
+/*
  * JsonKeyValue -
  *		untransformed representation of JSON object key-value pair for
  *		JSON_OBJECT() and JSON_OBJECTAGG()
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 439a16aa62e..290898cfd75 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -73,8 +73,14 @@ 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.
+ * TableFunc - node for a table function, such as XMLTABLE or JSON_TABLE.
  *
  * Entries in the ns_names list are either String nodes containing
  * literal namespace names, or NULL pointers to represent DEFAULT.
@@ -82,6 +88,7 @@ typedef struct RangeVar
 typedef struct TableFunc
 {
 	NodeTag		type;
+	TableFuncType functype;		/* XMLTABLE or JSON_TABLE */
 	List	   *ns_uris;		/* list of namespace URI expressions */
 	List	   *ns_names;		/* list of namespace names or NULL */
 	Node	   *docexpr;		/* input document expression */
@@ -92,7 +99,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;
@@ -1241,7 +1250,8 @@ typedef enum JsonExprOp
 {
 	JSON_VALUE_OP,				/* JSON_VALUE() */
 	JSON_QUERY_OP,				/* JSON_QUERY() */
-	JSON_EXISTS_OP				/* JSON_EXISTS() */
+	JSON_EXISTS_OP,				/* JSON_EXISTS() */
+	JSON_TABLE_OP               /* JSON_TABLE() */
 } JsonExprOp;
 
 /*
@@ -1455,6 +1465,31 @@ typedef struct JsonExpr
 	int			location;		/* token location, or -1 if unknown */
 } JsonExpr;
 
+/*
+ * JsonTableParent -
+ *		transformed representation of parent JSON_TABLE plan node
+ */
+typedef struct JsonTableParent
+{
+	NodeTag		type;
+	Const	   *path;		/* jsonpath constant */
+	Node	   *child;		/* nested columns, if any */
+	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 */
+} JsonTableParent;
+
+/*
+ * JsonTableSibling -
+ *		transformed representation of joined sibling JSON_TABLE plan node
+ */
+typedef struct JsonTableSibling
+{
+	NodeTag		type;
+	Node	   *larg;		/* left join node */
+	Node	   *rarg;		/* right join node */
+} JsonTableSibling;
+
 /* ----------------
  * NullTest
  *
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index a73032b319d..9097ce7b26d 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -241,6 +241,7 @@ PG_KEYWORD("json_objectagg", JSON_OBJECTAGG, COL_NAME_KEYWORD, BARE_LABEL)
 PG_KEYWORD("json_query", JSON_QUERY, COL_NAME_KEYWORD, BARE_LABEL)
 PG_KEYWORD("json_scalar", JSON_SCALAR, COL_NAME_KEYWORD, BARE_LABEL)
 PG_KEYWORD("json_serialize", JSON_SERIALIZE, COL_NAME_KEYWORD, BARE_LABEL)
+PG_KEYWORD("json_table", JSON_TABLE, COL_NAME_KEYWORD, BARE_LABEL)
 PG_KEYWORD("json_value", JSON_VALUE, COL_NAME_KEYWORD, BARE_LABEL)
 PG_KEYWORD("keep", KEEP, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("key", KEY, UNRESERVED_KEYWORD, BARE_LABEL)
@@ -283,6 +284,7 @@ PG_KEYWORD("names", NAMES, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("national", NATIONAL, COL_NAME_KEYWORD, BARE_LABEL)
 PG_KEYWORD("natural", NATURAL, TYPE_FUNC_NAME_KEYWORD, BARE_LABEL)
 PG_KEYWORD("nchar", NCHAR, COL_NAME_KEYWORD, BARE_LABEL)
+PG_KEYWORD("nested", NESTED, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("new", NEW, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("next", NEXT, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("nfc", NFC, UNRESERVED_KEYWORD, BARE_LABEL)
@@ -332,6 +334,7 @@ PG_KEYWORD("partial", PARTIAL, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("partition", PARTITION, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("passing", PASSING, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("password", PASSWORD, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("path", PATH, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("placing", PLACING, RESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("plans", PLANS, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("policy", POLICY, UNRESERVED_KEYWORD, BARE_LABEL)
diff --git a/src/include/parser/parse_clause.h b/src/include/parser/parse_clause.h
index 2495c300348..e86b0023922 100644
--- a/src/include/parser/parse_clause.h
+++ b/src/include/parser/parse_clause.h
@@ -51,4 +51,7 @@ extern List *addTargetToSortList(ParseState *pstate, TargetEntry *tle,
 extern Index assignSortGroupRef(TargetEntry *tle, List *tlist);
 extern bool targetIsInSortList(TargetEntry *tle, Oid sortop, List *sortList);
 
+/* functions in parse_jsontable.c */
+extern ParseNamespaceItem *transformJsonTable(ParseState *pstate, JsonTable *jt);
+
 #endif							/* PARSE_CLAUSE_H */
diff --git a/src/include/utils/jsonpath.h b/src/include/utils/jsonpath.h
index 98a61d7f72f..1897ddffa6e 100644
--- a/src/include/utils/jsonpath.h
+++ b/src/include/utils/jsonpath.h
@@ -15,6 +15,7 @@
 #define JSONPATH_H
 
 #include "fmgr.h"
+#include "executor/tablefunc.h"
 #include "nodes/pg_list.h"
 #include "nodes/primnodes.h"
 #include "utils/jsonb.h"
@@ -263,6 +264,7 @@ typedef struct JsonPathVariableEvalContext
 	int32		typmod;
 	struct ExprContext *econtext;
 	struct ExprState  *estate;
+	MemoryContext mcxt;		/* memory context for cached value */
 	Datum		value;
 	bool		isnull;
 	bool		evaluated;
@@ -281,4 +283,6 @@ extern JsonbValue *JsonPathValue(Datum jb, JsonPath *jp, bool *empty,
 extern int EvalJsonPathVar(void *vars, char *varName, int varNameLen,
 						   JsonbValue *val, JsonbValue *baseObject);
 
+extern const TableFuncRoutine JsonbTableRoutine;
+
 #endif