sql/json remaining issue

From c13ad4db322c57e1fb6c040ff2f85e957b9d0e79 Mon Sep 17 00:00:00 2001
From: Amit Langote <amitlan@postgresql.org>
Date: Tue, 9 Apr 2024 20:31:30 +0900
Subject: [PATCH v1] JSON_TABLE(): mention column name in the ON EMPTY error
 message

---
 src/backend/executor/execExprInterp.c         | 36 ++++++++++---------
 src/backend/parser/parse_expr.c               |  1 +
 src/backend/parser/parse_jsontable.c          |  1 +
 src/include/nodes/parsenodes.h                |  2 ++
 src/include/nodes/primnodes.h                 |  3 ++
 .../regress/expected/sqljson_jsontable.out    |  6 ++--
 6 files changed, 30 insertions(+), 19 deletions(-)

diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index 41af28cb1e..0becee67a8 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -4409,28 +4409,32 @@ ExecEvalJsonExprPath(ExprState *state, ExprEvalStep *op,
 	{
 		if (jsexpr->on_empty)
 		{
-			if (jsexpr->on_empty->btype == JSON_BEHAVIOR_ERROR)
-				ereport(ERROR,
-						errcode(ERRCODE_NO_SQL_JSON_ITEM),
-						errmsg("no SQL/JSON item"));
-			else
+			if (jsexpr->on_empty->btype != JSON_BEHAVIOR_ERROR)
+			{
 				jsestate->empty.value = BoolGetDatum(true);
+				Assert(jsestate->jump_empty >= 0);
+				return jsestate->jump_empty;
+			}
+		}
+		else if (jsexpr->on_error->btype != JSON_BEHAVIOR_ERROR)
+		{
+			jsestate->error.value = BoolGetDatum(true);
 
-			Assert(jsestate->jump_empty >= 0);
-			return jsestate->jump_empty;
+			*op->resvalue = (Datum) 0;
+			*op->resnull = true;
+			Assert(!throw_error && jsestate->jump_error >= 0);
+			return jsestate->jump_error;
 		}
-		else if (jsexpr->on_error->btype == JSON_BEHAVIOR_ERROR)
+
+		if (jsexpr->column_name)
 			ereport(ERROR,
 					errcode(ERRCODE_NO_SQL_JSON_ITEM),
-					errmsg("no SQL/JSON item"));
+					errmsg("no SQL/JSON item found for column \"%s\"",
+						   jsexpr->column_name));
 		else
-			jsestate->error.value = BoolGetDatum(true);
-
-		*op->resvalue = (Datum) 0;
-		*op->resnull = true;
-
-		Assert(!throw_error && jsestate->jump_error >= 0);
-		return jsestate->jump_error;
+			ereport(ERROR,
+					errcode(ERRCODE_NO_SQL_JSON_ITEM),
+					errmsg("no SQL/JSON item"));
 	}
 
 	/*
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 4c98d7a046..34ac17868b 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -4311,6 +4311,7 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
 	jsexpr = makeNode(JsonExpr);
 	jsexpr->location = func->location;
 	jsexpr->op = func->op;
+	jsexpr->column_name = func->column_name;
 
 	/*
 	 * jsonpath machinery can only handle jsonb documents, so coerce the input
diff --git a/src/backend/parser/parse_jsontable.c b/src/backend/parser/parse_jsontable.c
index 99d3101f6b..b3920f0531 100644
--- a/src/backend/parser/parse_jsontable.c
+++ b/src/backend/parser/parse_jsontable.c
@@ -327,6 +327,7 @@ transformJsonTableColumns(JsonTableParseContext *cxt, List *columns,
 
 					jfe = transformJsonTableColumn(rawc, (Node *) param,
 												   passingArgs);
+					jfe->column_name = pstrdup(rawc->name);
 
 					colexpr = transformExpr(pstate, (Node *) jfe,
 											EXPR_KIND_FROM_FUNCTION);
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index f763f790b1..c0ff75e643 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1791,6 +1791,8 @@ typedef struct JsonFuncExpr
 {
 	NodeTag		type;
 	JsonExprOp	op;				/* expression type */
+	char	   *column_name;	/* JSON_TABLE() column name or NULL if this is
+								 * not for a JSON_TABLE() */
 	JsonValueExpr *context_item;	/* context item expression */
 	Node	   *pathspec;		/* JSON path specification expression */
 	List	   *passing;		/* list of PASSING clause arguments, if any */
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index dafe93a4c9..9b662b8dd2 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -1782,6 +1782,9 @@ typedef struct JsonExpr
 
 	JsonExprOp	op;
 
+	char	   *column_name;	/* JSON_TABLE() column name or NULL if this is
+								 * not for a JSON_TABLE() */
+
 	/* jsonb-valued expression to query */
 	Node	   *formatted_expr;
 
diff --git a/src/test/regress/expected/sqljson_jsontable.out b/src/test/regress/expected/sqljson_jsontable.out
index a00eec8a6f..849a02d9c0 100644
--- a/src/test/regress/expected/sqljson_jsontable.out
+++ b/src/test/regress/expected/sqljson_jsontable.out
@@ -492,11 +492,11 @@ FROM
 		ON true;
 ERROR:  invalid input syntax for type integer: "err"
 SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH '$.a' ERROR ON EMPTY)) jt;
-ERROR:  no SQL/JSON item
+ERROR:  no SQL/JSON item found for column "a"
 SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH 'strict $.a' ERROR ON ERROR) ERROR ON ERROR) jt;
 ERROR:  jsonpath member accessor can only be applied to an object
 SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH 'lax $.a' ERROR ON EMPTY) ERROR ON ERROR) jt;
-ERROR:  no SQL/JSON item
+ERROR:  no SQL/JSON item found for column "a"
 SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH '$'   DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt;
  a 
 ---
@@ -849,7 +849,7 @@ SELECT sub.* FROM s,
 		xx int path '$.c',
 		NESTED PATH '$.a.za[1]' columns (NESTED PATH '$.z21[*]' COLUMNS (z21 int path '$?(@ >= $"x")' ERROR ON ERROR))
 	)) sub;
-ERROR:  no SQL/JSON item
+ERROR:  no SQL/JSON item found for column "z21"
 -- Parent columns xx1, xx appear before NESTED ones
 SELECT sub.* FROM s,
 	(VALUES (23)) x(x), generate_series(13, 13) y,
-- 
2.43.0

From f6f60653da88543f1d1074f217b356dfd0832c58 Mon Sep 17 00:00:00 2001
From: Amit Langote <amitlan@postgresql.org>
Date: Tue, 9 Apr 2024 20:31:30 +0900
Subject: [PATCH v2] JSON_TABLE(): mention column name in some error messages

---
 src/backend/executor/execExprInterp.c         | 42 +++++++++--------
 src/backend/parser/parse_expr.c               |  1 +
 src/backend/parser/parse_jsontable.c          |  9 ++--
 src/backend/utils/adt/jsonpath_exec.c         | 45 ++++++++++++++-----
 src/include/nodes/parsenodes.h                |  2 +
 src/include/nodes/primnodes.h                 |  3 ++
 src/include/utils/jsonpath.h                  |  6 ++-
 .../regress/expected/sqljson_jsontable.out    | 10 +++--
 src/test/regress/sql/sqljson_jsontable.sql    |  3 ++
 9 files changed, 80 insertions(+), 41 deletions(-)

diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index 41af28cb1e..580c897144 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -4312,7 +4312,8 @@ ExecEvalJsonExprPath(ExprState *state, ExprEvalStep *op,
 		case JSON_QUERY_OP:
 			*op->resvalue = JsonPathQuery(item, path, jsexpr->wrapper, &empty,
 										  !throw_error ? &error : NULL,
-										  jsestate->args);
+										  jsestate->args,
+										  jsexpr->column_name);
 
 			*op->resnull = (DatumGetPointer(*op->resvalue) == NULL);
 
@@ -4337,7 +4338,8 @@ ExecEvalJsonExprPath(ExprState *state, ExprEvalStep *op,
 			{
 				JsonbValue *jbv = JsonPathValue(item, path, &empty,
 												!throw_error ? &error : NULL,
-												jsestate->args);
+												jsestate->args,
+												jsexpr->column_name);
 
 				if (jbv == NULL)
 				{
@@ -4409,28 +4411,32 @@ ExecEvalJsonExprPath(ExprState *state, ExprEvalStep *op,
 	{
 		if (jsexpr->on_empty)
 		{
-			if (jsexpr->on_empty->btype == JSON_BEHAVIOR_ERROR)
-				ereport(ERROR,
-						errcode(ERRCODE_NO_SQL_JSON_ITEM),
-						errmsg("no SQL/JSON item"));
-			else
+			if (jsexpr->on_empty->btype != JSON_BEHAVIOR_ERROR)
+			{
 				jsestate->empty.value = BoolGetDatum(true);
+				Assert(jsestate->jump_empty >= 0);
+				return jsestate->jump_empty;
+			}
+		}
+		else if (jsexpr->on_error->btype != JSON_BEHAVIOR_ERROR)
+		{
+			jsestate->error.value = BoolGetDatum(true);
 
-			Assert(jsestate->jump_empty >= 0);
-			return jsestate->jump_empty;
+			*op->resvalue = (Datum) 0;
+			*op->resnull = true;
+			Assert(!throw_error && jsestate->jump_error >= 0);
+			return jsestate->jump_error;
 		}
-		else if (jsexpr->on_error->btype == JSON_BEHAVIOR_ERROR)
+
+		if (jsexpr->column_name)
 			ereport(ERROR,
 					errcode(ERRCODE_NO_SQL_JSON_ITEM),
-					errmsg("no SQL/JSON item"));
+					errmsg("no SQL/JSON item found for column \"%s\"",
+						   jsexpr->column_name));
 		else
-			jsestate->error.value = BoolGetDatum(true);
-
-		*op->resvalue = (Datum) 0;
-		*op->resnull = true;
-
-		Assert(!throw_error && jsestate->jump_error >= 0);
-		return jsestate->jump_error;
+			ereport(ERROR,
+					errcode(ERRCODE_NO_SQL_JSON_ITEM),
+					errmsg("no SQL/JSON item"));
 	}
 
 	/*
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 4c98d7a046..34ac17868b 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -4311,6 +4311,7 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
 	jsexpr = makeNode(JsonExpr);
 	jsexpr->location = func->location;
 	jsexpr->op = func->op;
+	jsexpr->column_name = func->column_name;
 
 	/*
 	 * jsonpath machinery can only handle jsonb documents, so coerce the input
diff --git a/src/backend/parser/parse_jsontable.c b/src/backend/parser/parse_jsontable.c
index 99d3101f6b..70b00a45f0 100644
--- a/src/backend/parser/parse_jsontable.c
+++ b/src/backend/parser/parse_jsontable.c
@@ -402,12 +402,6 @@ transformJsonTableColumn(JsonTableColumn *jtc, Node *contextItemExpr,
 	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)
@@ -415,6 +409,9 @@ transformJsonTableColumn(JsonTableColumn *jtc, Node *contextItemExpr,
 	else
 		jfexpr->op = JSON_QUERY_OP;
 
+	/* Pass the column name so any runtime JsonExpr errors can print it. */
+	jfexpr->column_name = pstrdup(jtc->name);
+
 	jfexpr->context_item = makeJsonValueExpr((Expr *) contextItemExpr, NULL,
 											 makeJsonFormat(JS_FORMAT_DEFAULT,
 															JS_ENC_DEFAULT,
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index 103572ed93..1cc6bfec8b 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -3899,7 +3899,8 @@ JsonPathExists(Datum jb, JsonPath *jp, bool *error, List *vars)
  */
 Datum
 JsonPathQuery(Datum jb, JsonPath *jp, JsonWrapper wrapper, bool *empty,
-			  bool *error, List *vars)
+			  bool *error, List *vars,
+			  const char *column_name)
 {
 	JsonbValue *singleton;
 	bool		wrap;
@@ -3950,10 +3951,17 @@ JsonPathQuery(Datum jb, JsonPath *jp, JsonWrapper wrapper, bool *empty,
 			return (Datum) 0;
 		}
 
-		ereport(ERROR,
-				(errcode(ERRCODE_MORE_THAN_ONE_SQL_JSON_ITEM),
-				 errmsg("JSON path expression in JSON_QUERY should return singleton item without wrapper"),
-				 errhint("Use WITH WRAPPER clause to wrap SQL/JSON item sequence into array.")));
+		if (column_name)
+			ereport(ERROR,
+					(errcode(ERRCODE_MORE_THAN_ONE_SQL_JSON_ITEM),
+					 errmsg("JSON path expression for column \"%s\" should return singleton item without wrapper",
+							column_name),
+					 errhint("Use WITH WRAPPER clause to wrap SQL/JSON item sequence into array.")));
+		else
+			ereport(ERROR,
+					(errcode(ERRCODE_MORE_THAN_ONE_SQL_JSON_ITEM),
+					 errmsg("JSON path expression in JSON_QUERY should return singleton item without wrapper"),
+					 errhint("Use WITH WRAPPER clause to wrap SQL/JSON item sequence into array.")));
 	}
 
 	if (singleton)
@@ -3970,7 +3978,8 @@ JsonPathQuery(Datum jb, JsonPath *jp, JsonWrapper wrapper, bool *empty,
  * *error to true.  *empty is set to true if no match is found.
  */
 JsonbValue *
-JsonPathValue(Datum jb, JsonPath *jp, bool *empty, bool *error, List *vars)
+JsonPathValue(Datum jb, JsonPath *jp, bool *empty, bool *error, List *vars,
+			  const char *column_name)
 {
 	JsonbValue *res;
 	JsonValueList found = {0};
@@ -4006,9 +4015,15 @@ JsonPathValue(Datum jb, JsonPath *jp, bool *empty, bool *error, List *vars)
 			return NULL;
 		}
 
-		ereport(ERROR,
-				(errcode(ERRCODE_MORE_THAN_ONE_SQL_JSON_ITEM),
-				 errmsg("JSON path expression in JSON_VALUE should return singleton scalar item")));
+		if (column_name)
+			ereport(ERROR,
+					(errcode(ERRCODE_MORE_THAN_ONE_SQL_JSON_ITEM),
+					 errmsg("JSON path expression for column \"%s\" should return singleton scalar item",
+							column_name)));
+		else
+			ereport(ERROR,
+					(errcode(ERRCODE_SQL_JSON_SCALAR_REQUIRED),
+					 errmsg("JSON path expression in JSON_VALUE should return singleton scalar item")));
 	}
 
 	res = JsonValueListHead(&found);
@@ -4024,9 +4039,15 @@ JsonPathValue(Datum jb, JsonPath *jp, bool *empty, bool *error, List *vars)
 			return NULL;
 		}
 
-		ereport(ERROR,
-				(errcode(ERRCODE_SQL_JSON_SCALAR_REQUIRED),
-				 errmsg("JSON path expression in JSON_VALUE should return singleton scalar item")));
+		if (column_name)
+			ereport(ERROR,
+					(errcode(ERRCODE_MORE_THAN_ONE_SQL_JSON_ITEM),
+					 errmsg("JSON path expression for column \"%s\" should return singleton scalar item",
+							column_name)));
+		else
+			ereport(ERROR,
+					(errcode(ERRCODE_SQL_JSON_SCALAR_REQUIRED),
+					 errmsg("JSON path expression in JSON_VALUE should return singleton scalar item")));
 	}
 
 	if (res->type == jbvNull)
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index f763f790b1..c0ff75e643 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1791,6 +1791,8 @@ typedef struct JsonFuncExpr
 {
 	NodeTag		type;
 	JsonExprOp	op;				/* expression type */
+	char	   *column_name;	/* JSON_TABLE() column name or NULL if this is
+								 * not for a JSON_TABLE() */
 	JsonValueExpr *context_item;	/* context item expression */
 	Node	   *pathspec;		/* JSON path specification expression */
 	List	   *passing;		/* list of PASSING clause arguments, if any */
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index dafe93a4c9..9b662b8dd2 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -1782,6 +1782,9 @@ typedef struct JsonExpr
 
 	JsonExprOp	op;
 
+	char	   *column_name;	/* JSON_TABLE() column name or NULL if this is
+								 * not for a JSON_TABLE() */
+
 	/* jsonb-valued expression to query */
 	Node	   *formatted_expr;
 
diff --git a/src/include/utils/jsonpath.h b/src/include/utils/jsonpath.h
index 4d3964488d..0bcc1ac569 100644
--- a/src/include/utils/jsonpath.h
+++ b/src/include/utils/jsonpath.h
@@ -300,9 +300,11 @@ typedef struct JsonPathVariable
 /* SQL/JSON item */
 extern bool JsonPathExists(Datum jb, JsonPath *path, bool *error, List *vars);
 extern Datum JsonPathQuery(Datum jb, JsonPath *jp, JsonWrapper wrapper,
-						   bool *empty, bool *error, List *vars);
+						   bool *empty, bool *error, List *vars,
+						   const char *column_name);
 extern JsonbValue *JsonPathValue(Datum jb, JsonPath *jp, bool *empty,
-								 bool *error, List *vars);
+								 bool *error, List *vars,
+								 const char *column_name);
 
 extern PGDLLIMPORT const TableFuncRoutine JsonbTableRoutine;
 
diff --git a/src/test/regress/expected/sqljson_jsontable.out b/src/test/regress/expected/sqljson_jsontable.out
index a00eec8a6f..6bbc7b46cf 100644
--- a/src/test/regress/expected/sqljson_jsontable.out
+++ b/src/test/regress/expected/sqljson_jsontable.out
@@ -492,11 +492,11 @@ FROM
 		ON true;
 ERROR:  invalid input syntax for type integer: "err"
 SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH '$.a' ERROR ON EMPTY)) jt;
-ERROR:  no SQL/JSON item
+ERROR:  no SQL/JSON item found for column "a"
 SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH 'strict $.a' ERROR ON ERROR) ERROR ON ERROR) jt;
 ERROR:  jsonpath member accessor can only be applied to an object
 SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH 'lax $.a' ERROR ON EMPTY) ERROR ON ERROR) jt;
-ERROR:  no SQL/JSON item
+ERROR:  no SQL/JSON item found for column "a"
 SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH '$'   DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt;
  a 
 ---
@@ -637,6 +637,10 @@ SELECT * FROM JSON_TABLE(jsonb '{"a": 123}', '$' || '.' || 'a' COLUMNS (foo int)
 ERROR:  only string constants are supported in JSON_TABLE path specification
 LINE 1: SELECT * FROM JSON_TABLE(jsonb '{"a": 123}', '$' || '.' || '...
                                                      ^
+-- JsonPathQuery() error message mentioning column name
+SELECT * FROM JSON_TABLE('{"a": [{"b": "1"}, {"b": "2"}]}', '$' COLUMNS (b json path '$.a[*].b' ERROR ON ERROR));
+ERROR:  JSON path expression for column "b" should return singleton item without wrapper
+HINT:  Use WITH WRAPPER clause to wrap SQL/JSON item sequence into array.
 -- JSON_TABLE: nested paths
 -- Duplicate path names
 SELECT * FROM JSON_TABLE(
@@ -849,7 +853,7 @@ SELECT sub.* FROM s,
 		xx int path '$.c',
 		NESTED PATH '$.a.za[1]' columns (NESTED PATH '$.z21[*]' COLUMNS (z21 int path '$?(@ >= $"x")' ERROR ON ERROR))
 	)) sub;
-ERROR:  no SQL/JSON item
+ERROR:  no SQL/JSON item found for column "z21"
 -- Parent columns xx1, xx appear before NESTED ones
 SELECT sub.* FROM s,
 	(VALUES (23)) x(x), generate_series(13, 13) y,
diff --git a/src/test/regress/sql/sqljson_jsontable.sql b/src/test/regress/sql/sqljson_jsontable.sql
index 3752ccc446..29c0c6ba52 100644
--- a/src/test/regress/sql/sqljson_jsontable.sql
+++ b/src/test/regress/sql/sqljson_jsontable.sql
@@ -290,6 +290,9 @@ FROM JSON_TABLE(
 -- Should fail (not supported)
 SELECT * FROM JSON_TABLE(jsonb '{"a": 123}', '$' || '.' || 'a' COLUMNS (foo int));
 
+-- JsonPathQuery() error message mentioning column name
+SELECT * FROM JSON_TABLE('{"a": [{"b": "1"}, {"b": "2"}]}', '$' COLUMNS (b json path '$.a[*].b' ERROR ON ERROR));
+
 -- JSON_TABLE: nested paths
 
 -- Duplicate path names
-- 
2.43.0

From 829a8b42e90de3792dda337a55e053c9d764deea Mon Sep 17 00:00:00 2001
From: Amit Langote <amitlan@postgresql.org>
Date: Tue, 9 Apr 2024 20:31:30 +0900
Subject: [PATCH v3 1/3] JSON_TABLE(): mention column name in some error
 messages

This modifies the various error messages in SQL/JSON query functions
that are internally called to evaluate JSON_TABLE() columns' path
expressions to mention the column's name.  To do so, a new field
column_name is added to both JsonFuncExpr and JsonExpr that is only
populated when creating those nodes for JSON_TABLE() columns.

While at it, the error messages are reworded for clarity.

Reported-by: Jian He <jian.universality@gmail.com>
Suggested-by: Jian He <jian.universality@gmail.com>
Discussion: https://postgr.es/m/CACJufxG_e0QLCgaELrr2ZNz7AxPeGCNKAORe3fHtFCQLsH4J4Q@mail.gmail.com
---
 src/backend/executor/execExprInterp.c         | 42 +++++++++--------
 src/backend/parser/parse_expr.c               |  1 +
 src/backend/parser/parse_jsontable.c          |  9 ++--
 src/backend/utils/adt/jsonpath_exec.c         | 45 ++++++++++++++-----
 src/include/nodes/parsenodes.h                |  2 +
 src/include/nodes/primnodes.h                 |  3 ++
 src/include/utils/jsonpath.h                  |  6 ++-
 .../regress/expected/sqljson_jsontable.out    | 10 +++--
 .../regress/expected/sqljson_queryfuncs.out   | 10 ++---
 src/test/regress/sql/sqljson_jsontable.sql    |  3 ++
 10 files changed, 85 insertions(+), 46 deletions(-)

diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index 41af28cb1e..580c897144 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -4312,7 +4312,8 @@ ExecEvalJsonExprPath(ExprState *state, ExprEvalStep *op,
 		case JSON_QUERY_OP:
 			*op->resvalue = JsonPathQuery(item, path, jsexpr->wrapper, &empty,
 										  !throw_error ? &error : NULL,
-										  jsestate->args);
+										  jsestate->args,
+										  jsexpr->column_name);
 
 			*op->resnull = (DatumGetPointer(*op->resvalue) == NULL);
 
@@ -4337,7 +4338,8 @@ ExecEvalJsonExprPath(ExprState *state, ExprEvalStep *op,
 			{
 				JsonbValue *jbv = JsonPathValue(item, path, &empty,
 												!throw_error ? &error : NULL,
-												jsestate->args);
+												jsestate->args,
+												jsexpr->column_name);
 
 				if (jbv == NULL)
 				{
@@ -4409,28 +4411,32 @@ ExecEvalJsonExprPath(ExprState *state, ExprEvalStep *op,
 	{
 		if (jsexpr->on_empty)
 		{
-			if (jsexpr->on_empty->btype == JSON_BEHAVIOR_ERROR)
-				ereport(ERROR,
-						errcode(ERRCODE_NO_SQL_JSON_ITEM),
-						errmsg("no SQL/JSON item"));
-			else
+			if (jsexpr->on_empty->btype != JSON_BEHAVIOR_ERROR)
+			{
 				jsestate->empty.value = BoolGetDatum(true);
+				Assert(jsestate->jump_empty >= 0);
+				return jsestate->jump_empty;
+			}
+		}
+		else if (jsexpr->on_error->btype != JSON_BEHAVIOR_ERROR)
+		{
+			jsestate->error.value = BoolGetDatum(true);
 
-			Assert(jsestate->jump_empty >= 0);
-			return jsestate->jump_empty;
+			*op->resvalue = (Datum) 0;
+			*op->resnull = true;
+			Assert(!throw_error && jsestate->jump_error >= 0);
+			return jsestate->jump_error;
 		}
-		else if (jsexpr->on_error->btype == JSON_BEHAVIOR_ERROR)
+
+		if (jsexpr->column_name)
 			ereport(ERROR,
 					errcode(ERRCODE_NO_SQL_JSON_ITEM),
-					errmsg("no SQL/JSON item"));
+					errmsg("no SQL/JSON item found for column \"%s\"",
+						   jsexpr->column_name));
 		else
-			jsestate->error.value = BoolGetDatum(true);
-
-		*op->resvalue = (Datum) 0;
-		*op->resnull = true;
-
-		Assert(!throw_error && jsestate->jump_error >= 0);
-		return jsestate->jump_error;
+			ereport(ERROR,
+					errcode(ERRCODE_NO_SQL_JSON_ITEM),
+					errmsg("no SQL/JSON item"));
 	}
 
 	/*
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 4c98d7a046..34ac17868b 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -4311,6 +4311,7 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
 	jsexpr = makeNode(JsonExpr);
 	jsexpr->location = func->location;
 	jsexpr->op = func->op;
+	jsexpr->column_name = func->column_name;
 
 	/*
 	 * jsonpath machinery can only handle jsonb documents, so coerce the input
diff --git a/src/backend/parser/parse_jsontable.c b/src/backend/parser/parse_jsontable.c
index 99d3101f6b..70b00a45f0 100644
--- a/src/backend/parser/parse_jsontable.c
+++ b/src/backend/parser/parse_jsontable.c
@@ -402,12 +402,6 @@ transformJsonTableColumn(JsonTableColumn *jtc, Node *contextItemExpr,
 	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)
@@ -415,6 +409,9 @@ transformJsonTableColumn(JsonTableColumn *jtc, Node *contextItemExpr,
 	else
 		jfexpr->op = JSON_QUERY_OP;
 
+	/* Pass the column name so any runtime JsonExpr errors can print it. */
+	jfexpr->column_name = pstrdup(jtc->name);
+
 	jfexpr->context_item = makeJsonValueExpr((Expr *) contextItemExpr, NULL,
 											 makeJsonFormat(JS_FORMAT_DEFAULT,
 															JS_ENC_DEFAULT,
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index 103572ed93..e74dc1b2d4 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -3899,7 +3899,8 @@ JsonPathExists(Datum jb, JsonPath *jp, bool *error, List *vars)
  */
 Datum
 JsonPathQuery(Datum jb, JsonPath *jp, JsonWrapper wrapper, bool *empty,
-			  bool *error, List *vars)
+			  bool *error, List *vars,
+			  const char *column_name)
 {
 	JsonbValue *singleton;
 	bool		wrap;
@@ -3950,10 +3951,17 @@ JsonPathQuery(Datum jb, JsonPath *jp, JsonWrapper wrapper, bool *empty,
 			return (Datum) 0;
 		}
 
-		ereport(ERROR,
-				(errcode(ERRCODE_MORE_THAN_ONE_SQL_JSON_ITEM),
-				 errmsg("JSON path expression in JSON_QUERY should return singleton item without wrapper"),
-				 errhint("Use WITH WRAPPER clause to wrap SQL/JSON item sequence into array.")));
+		if (column_name)
+			ereport(ERROR,
+					(errcode(ERRCODE_MORE_THAN_ONE_SQL_JSON_ITEM),
+					 errmsg("JSON path expression for column \"%s\" should return single item without wrapper",
+							column_name),
+					 errhint("Use WITH WRAPPER clause to wrap SQL/JSON items into array.")));
+		else
+			ereport(ERROR,
+					(errcode(ERRCODE_MORE_THAN_ONE_SQL_JSON_ITEM),
+					 errmsg("JSON path expression in JSON_QUERY should return single item without wrapper"),
+					 errhint("Use WITH WRAPPER clause to wrap SQL/JSON items into array.")));
 	}
 
 	if (singleton)
@@ -3970,7 +3978,8 @@ JsonPathQuery(Datum jb, JsonPath *jp, JsonWrapper wrapper, bool *empty,
  * *error to true.  *empty is set to true if no match is found.
  */
 JsonbValue *
-JsonPathValue(Datum jb, JsonPath *jp, bool *empty, bool *error, List *vars)
+JsonPathValue(Datum jb, JsonPath *jp, bool *empty, bool *error, List *vars,
+			  const char *column_name)
 {
 	JsonbValue *res;
 	JsonValueList found = {0};
@@ -4006,9 +4015,15 @@ JsonPathValue(Datum jb, JsonPath *jp, bool *empty, bool *error, List *vars)
 			return NULL;
 		}
 
-		ereport(ERROR,
-				(errcode(ERRCODE_MORE_THAN_ONE_SQL_JSON_ITEM),
-				 errmsg("JSON path expression in JSON_VALUE should return singleton scalar item")));
+		if (column_name)
+			ereport(ERROR,
+					(errcode(ERRCODE_MORE_THAN_ONE_SQL_JSON_ITEM),
+					 errmsg("JSON path expression for column \"%s\" should return single scalar item",
+							column_name)));
+		else
+			ereport(ERROR,
+					(errcode(ERRCODE_MORE_THAN_ONE_SQL_JSON_ITEM),
+					 errmsg("JSON path expression in JSON_VALUE should return single scalar item")));
 	}
 
 	res = JsonValueListHead(&found);
@@ -4024,9 +4039,15 @@ JsonPathValue(Datum jb, JsonPath *jp, bool *empty, bool *error, List *vars)
 			return NULL;
 		}
 
-		ereport(ERROR,
-				(errcode(ERRCODE_SQL_JSON_SCALAR_REQUIRED),
-				 errmsg("JSON path expression in JSON_VALUE should return singleton scalar item")));
+		if (column_name)
+			ereport(ERROR,
+					(errcode(ERRCODE_SQL_JSON_SCALAR_REQUIRED),
+					 errmsg("JSON path expression for column \"%s\" should return single scalar item",
+							column_name)));
+		else
+			ereport(ERROR,
+					(errcode(ERRCODE_SQL_JSON_SCALAR_REQUIRED),
+					 errmsg("JSON path expression in JSON_VALUE should return single scalar item")));
 	}
 
 	if (res->type == jbvNull)
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index f763f790b1..c0ff75e643 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1791,6 +1791,8 @@ typedef struct JsonFuncExpr
 {
 	NodeTag		type;
 	JsonExprOp	op;				/* expression type */
+	char	   *column_name;	/* JSON_TABLE() column name or NULL if this is
+								 * not for a JSON_TABLE() */
 	JsonValueExpr *context_item;	/* context item expression */
 	Node	   *pathspec;		/* JSON path specification expression */
 	List	   *passing;		/* list of PASSING clause arguments, if any */
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index dafe93a4c9..9b662b8dd2 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -1782,6 +1782,9 @@ typedef struct JsonExpr
 
 	JsonExprOp	op;
 
+	char	   *column_name;	/* JSON_TABLE() column name or NULL if this is
+								 * not for a JSON_TABLE() */
+
 	/* jsonb-valued expression to query */
 	Node	   *formatted_expr;
 
diff --git a/src/include/utils/jsonpath.h b/src/include/utils/jsonpath.h
index 4d3964488d..0bcc1ac569 100644
--- a/src/include/utils/jsonpath.h
+++ b/src/include/utils/jsonpath.h
@@ -300,9 +300,11 @@ typedef struct JsonPathVariable
 /* SQL/JSON item */
 extern bool JsonPathExists(Datum jb, JsonPath *path, bool *error, List *vars);
 extern Datum JsonPathQuery(Datum jb, JsonPath *jp, JsonWrapper wrapper,
-						   bool *empty, bool *error, List *vars);
+						   bool *empty, bool *error, List *vars,
+						   const char *column_name);
 extern JsonbValue *JsonPathValue(Datum jb, JsonPath *jp, bool *empty,
-								 bool *error, List *vars);
+								 bool *error, List *vars,
+								 const char *column_name);
 
 extern PGDLLIMPORT const TableFuncRoutine JsonbTableRoutine;
 
diff --git a/src/test/regress/expected/sqljson_jsontable.out b/src/test/regress/expected/sqljson_jsontable.out
index a00eec8a6f..d65fa7fe69 100644
--- a/src/test/regress/expected/sqljson_jsontable.out
+++ b/src/test/regress/expected/sqljson_jsontable.out
@@ -492,11 +492,11 @@ FROM
 		ON true;
 ERROR:  invalid input syntax for type integer: "err"
 SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH '$.a' ERROR ON EMPTY)) jt;
-ERROR:  no SQL/JSON item
+ERROR:  no SQL/JSON item found for column "a"
 SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH 'strict $.a' ERROR ON ERROR) ERROR ON ERROR) jt;
 ERROR:  jsonpath member accessor can only be applied to an object
 SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH 'lax $.a' ERROR ON EMPTY) ERROR ON ERROR) jt;
-ERROR:  no SQL/JSON item
+ERROR:  no SQL/JSON item found for column "a"
 SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH '$'   DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt;
  a 
 ---
@@ -637,6 +637,10 @@ SELECT * FROM JSON_TABLE(jsonb '{"a": 123}', '$' || '.' || 'a' COLUMNS (foo int)
 ERROR:  only string constants are supported in JSON_TABLE path specification
 LINE 1: SELECT * FROM JSON_TABLE(jsonb '{"a": 123}', '$' || '.' || '...
                                                      ^
+-- JsonPathQuery() error message mentioning column name
+SELECT * FROM JSON_TABLE('{"a": [{"b": "1"}, {"b": "2"}]}', '$' COLUMNS (b json path '$.a[*].b' ERROR ON ERROR));
+ERROR:  JSON path expression for column "b" should return single item without wrapper
+HINT:  Use WITH WRAPPER clause to wrap SQL/JSON items into array.
 -- JSON_TABLE: nested paths
 -- Duplicate path names
 SELECT * FROM JSON_TABLE(
@@ -849,7 +853,7 @@ SELECT sub.* FROM s,
 		xx int path '$.c',
 		NESTED PATH '$.a.za[1]' columns (NESTED PATH '$.z21[*]' COLUMNS (z21 int path '$?(@ >= $"x")' ERROR ON ERROR))
 	)) sub;
-ERROR:  no SQL/JSON item
+ERROR:  no SQL/JSON item found for column "z21"
 -- Parent columns xx1, xx appear before NESTED ones
 SELECT sub.* FROM s,
 	(VALUES (23)) x(x), generate_series(13, 13) y,
diff --git a/src/test/regress/expected/sqljson_queryfuncs.out b/src/test/regress/expected/sqljson_queryfuncs.out
index 9e86b0da10..6c5f1b3ecd 100644
--- a/src/test/regress/expected/sqljson_queryfuncs.out
+++ b/src/test/regress/expected/sqljson_queryfuncs.out
@@ -339,7 +339,7 @@ SELECT JSON_VALUE(jsonb '[]', '$');
 (1 row)
 
 SELECT JSON_VALUE(jsonb '[]', '$' ERROR ON ERROR);
-ERROR:  JSON path expression in JSON_VALUE should return singleton scalar item
+ERROR:  JSON path expression in JSON_VALUE should return single scalar item
 SELECT JSON_VALUE(jsonb '{}', '$');
  json_value 
 ------------
@@ -347,7 +347,7 @@ SELECT JSON_VALUE(jsonb '{}', '$');
 (1 row)
 
 SELECT JSON_VALUE(jsonb '{}', '$' ERROR ON ERROR);
-ERROR:  JSON path expression in JSON_VALUE should return singleton scalar item
+ERROR:  JSON path expression in JSON_VALUE should return single scalar item
 SELECT JSON_VALUE(jsonb '1', '$.a');
  json_value 
 ------------
@@ -399,7 +399,7 @@ SELECT JSON_VALUE(jsonb '1', 'lax $.a' DEFAULT '2' ON EMPTY DEFAULT '3' ON ERROR
 SELECT JSON_VALUE(jsonb '1', 'lax $.a' ERROR ON EMPTY DEFAULT '3' ON ERROR);
 ERROR:  no SQL/JSON item
 SELECT JSON_VALUE(jsonb '[1,2]', '$[*]' ERROR ON ERROR);
-ERROR:  JSON path expression in JSON_VALUE should return singleton scalar item
+ERROR:  JSON path expression in JSON_VALUE should return single scalar item
 SELECT JSON_VALUE(jsonb '[1,2]', '$[*]' DEFAULT '0' ON ERROR);
  json_value 
 ------------
@@ -776,8 +776,8 @@ ERROR:  no SQL/JSON item
 SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON ERROR);
 ERROR:  no SQL/JSON item
 SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' ERROR ON ERROR);
-ERROR:  JSON path expression in JSON_QUERY should return singleton item without wrapper
-HINT:  Use WITH WRAPPER clause to wrap SQL/JSON item sequence into array.
+ERROR:  JSON path expression in JSON_QUERY should return single item without wrapper
+HINT:  Use WITH WRAPPER clause to wrap SQL/JSON items into array.
 SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' DEFAULT '"empty"' ON ERROR);
  json_query 
 ------------
diff --git a/src/test/regress/sql/sqljson_jsontable.sql b/src/test/regress/sql/sqljson_jsontable.sql
index 3752ccc446..29c0c6ba52 100644
--- a/src/test/regress/sql/sqljson_jsontable.sql
+++ b/src/test/regress/sql/sqljson_jsontable.sql
@@ -290,6 +290,9 @@ FROM JSON_TABLE(
 -- Should fail (not supported)
 SELECT * FROM JSON_TABLE(jsonb '{"a": 123}', '$' || '.' || 'a' COLUMNS (foo int));
 
+-- JsonPathQuery() error message mentioning column name
+SELECT * FROM JSON_TABLE('{"a": [{"b": "1"}, {"b": "2"}]}', '$' COLUMNS (b json path '$.a[*].b' ERROR ON ERROR));
+
 -- JSON_TABLE: nested paths
 
 -- Duplicate path names
-- 
2.43.0

From 7e948d959477db19f0a8732f976a921636ee159f Mon Sep 17 00:00:00 2001
From: Amit Langote <amitlan@postgresql.org>
Date: Fri, 12 Apr 2024 17:23:08 +0900
Subject: [PATCH v3 2/3] JSON_TABLE: Use term path_expression more consistently

SQL/JSON documentation uses <replaceable>path_expression</replaceable>
for JSON path expressions but the bits added in bb766cde63b used
<replaceable>json_path_specification</replaceable>.  Replace with
the former for consistency.

Reported-by: Jian He <jian.universality@gmail.com>
Discussion: https://postgr.es/m/CACJufxG_e0QLCgaELrr2ZNz7AxPeGCNKAORe3fHtFCQLsH4J4Q@mail.gmail.com
---
 doc/src/sgml/func.sgml | 4 ++--
 1 file changed, 2 insertions(+), 2 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 8dfb42ad4d..545b1437df 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -18942,7 +18942,7 @@ where <replaceable class="parameter">json_table_column</replaceable> is:
         <optional> { ERROR | NULL | EMPTY { ARRAY | OBJECT } | DEFAULT <replaceable>expression</replaceable> } ON ERROR </optional>
   | <replaceable>name</replaceable> <replaceable>type</replaceable> EXISTS <optional> PATH <replaceable>path_expression</replaceable> </optional>
         <optional> { ERROR | TRUE | FALSE | UNKNOWN } ON ERROR </optional>
-  | NESTED <optional> PATH </optional> <replaceable>json_path_specification</replaceable> <optional> AS <replaceable>json_path_name</replaceable> </optional> COLUMNS ( <replaceable>json_table_column</replaceable> <optional>, ...</optional> )
+  | NESTED <optional> PATH </optional> <replaceable>path_expression</replaceable> <optional> AS <replaceable>json_path_name</replaceable> </optional> COLUMNS ( <replaceable>json_table_column</replaceable> <optional>, ...</optional> )
 </synopsis>
 
   <para>
@@ -19083,7 +19083,7 @@ where <replaceable class="parameter">json_table_column</replaceable> is:
 
    <varlistentry>
     <term>
-      <literal>NESTED <optional> PATH </optional></literal> <replaceable>json_path_specification</replaceable> <optional> <literal>AS</literal> <replaceable>json_path_name</replaceable> </optional>
+      <literal>NESTED <optional> PATH </optional></literal> <replaceable>path_expression</replaceable> <optional> <literal>AS</literal> <replaceable>json_path_name</replaceable> </optional>
           <literal>COLUMNS</literal> ( <replaceable>json_table_column</replaceable> <optional>, ...</optional> )
     </term>
     <listitem>
-- 
2.43.0

From 0bd818a1ac3aeb08a6606ea63d5aeeb1be2c721e Mon Sep 17 00:00:00 2001
From: Amit Langote <amitlan@postgresql.org>
Date: Fri, 12 Apr 2024 17:17:31 +0900
Subject: [PATCH v3 3/3] SQL/JSON: Fix some comments in jsonpath.h

Reported-by: Jian He <jian.universality@gmail.com>
Discussion: https://postgr.es/m/CACJufxG_e0QLCgaELrr2ZNz7AxPeGCNKAORe3fHtFCQLsH4J4Q@mail.gmail.com
---
 src/include/utils/jsonpath.h | 8 +++-----
 1 file changed, 3 insertions(+), 5 deletions(-)

diff --git a/src/include/utils/jsonpath.h b/src/include/utils/jsonpath.h
index 0bcc1ac569..69c180c2e2 100644
--- a/src/include/utils/jsonpath.h
+++ b/src/include/utils/jsonpath.h
@@ -281,12 +281,9 @@ extern JsonPathParseResult *parsejsonpath(const char *str, int len,
 extern bool jspConvertRegexFlags(uint32 xflags, int *result,
 								 struct Node *escontext);
 
-
 /*
- * Evaluation of jsonpath
+ * Struct for details about external variables passed into jsonpath executor
  */
-
-/* External variable passed into jsonpath. */
 typedef struct JsonPathVariable
 {
 	char	   *name;
@@ -297,7 +294,7 @@ typedef struct JsonPathVariable
 } JsonPathVariable;
 
 
-/* SQL/JSON item */
+/* SQL/JSON query functions */
 extern bool JsonPathExists(Datum jb, JsonPath *path, bool *error, List *vars);
 extern Datum JsonPathQuery(Datum jb, JsonPath *jp, JsonWrapper wrapper,
 						   bool *empty, bool *error, List *vars,
@@ -306,6 +303,7 @@ extern JsonbValue *JsonPathValue(Datum jb, JsonPath *jp, bool *empty,
 								 bool *error, List *vars,
 								 const char *column_name);
 
+/* For JSON_TABLE() */
 extern PGDLLIMPORT const TableFuncRoutine JsonbTableRoutine;
 
 #endif
-- 
2.43.0

From 124cd4245266343daecdb4294b2013d9ebdd6b24 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Mon, 15 Apr 2024 12:37:36 +0800
Subject: [PATCH v1 1/1] in transformJsonBehavior better handle default
 expression

sql/json query functions for process json can return empty or error.
We can specify DEFAULT expression for handling empty or error cases while applying
the path expression to the json or while type coercion.
the default expression can be just a plain constant,
however, a constant can be formed as a cast expression,eg (1::jsonb::text).

so allow the DEFAULT expression formed as CoerceViaIO node
or CoerceToDomain node in transformJsonBehavior
for better handling these cases.
Discussion: https://postgr.es/m/CACJufxEqhqsfrg_p7EMyo5zak3d767iFDL8vz_4%3DZBHpOtrghw@mail.gmail.com

---
 src/backend/parser/parse_expr.c               |  4 +--
 .../regress/expected/sqljson_jsontable.out    | 25 ++++++++++++++++++
 .../regress/expected/sqljson_queryfuncs.out   | 26 +++++++++++++++++--
 src/test/regress/sql/sqljson_jsontable.sql    | 13 ++++++++++
 src/test/regress/sql/sqljson_queryfuncs.sql   |  7 +++++
 5 files changed, 71 insertions(+), 4 deletions(-)

diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 4c98d7a0..94dbb531 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -4636,10 +4636,10 @@ transformJsonBehavior(ParseState *pstate, JsonBehavior *behavior,
 		{
 			expr = transformExprRecurse(pstate, behavior->expr);
 			if (!IsA(expr, Const) && !IsA(expr, FuncExpr) &&
-				!IsA(expr, OpExpr))
+				!IsA(expr, OpExpr) && !IsA(expr, CoerceViaIO) && !IsA(expr, CoerceToDomain))
 				ereport(ERROR,
 						(errcode(ERRCODE_DATATYPE_MISMATCH),
-						 errmsg("can only specify a constant, non-aggregate function, or operator expression for DEFAULT"),
+						 errmsg("can only specify a constant, non-aggregate function, or operator expression or cast expression for DEFAULT"),
 						 parser_errposition(pstate, exprLocation(expr))));
 			if (contain_var_clause(expr))
 				ereport(ERROR,
diff --git a/src/test/regress/expected/sqljson_jsontable.out b/src/test/regress/expected/sqljson_jsontable.out
index a00eec8a..b45dd32a 100644
--- a/src/test/regress/expected/sqljson_jsontable.out
+++ b/src/test/regress/expected/sqljson_jsontable.out
@@ -113,6 +113,31 @@ FROM json_table_test vals
  [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 11 |     | "str"   | "str    |      |         | "str"   | "\"str\""    | "\"str\""
 (14 rows)
 
+--check default expression
+SELECT * FROM JSON_TABLE(jsonb '{"d1": "H"}', '$'
+    COLUMNS (js1 jsonb_test_domain path '$.a2' DEFAULT '"foo1"'::jsonb::text ON ERROR));
+  js1   
+--------
+ "foo1"
+(1 row)
+
+SELECT * FROM JSON_TABLE(jsonb '{"d1": "H"}', '$'
+    COLUMNS (js1 jsonb_test_domain path '$.a2' DEFAULT 'foo'::jsonb_test_domain ON ERROR));
+ERROR:  value for domain jsonb_test_domain violates check constraint "jsonb_test_domain_check"
+SELECT * FROM JSON_TABLE(jsonb '{"d1": "H"}', '$'
+    COLUMNS (js1 jsonb_test_domain path '$.a2' DEFAULT 'foo1'::jsonb_test_domain ON ERROR));
+ js1  
+------
+ foo1
+(1 row)
+
+SELECT * FROM JSON_TABLE(jsonb '{"d1": "foo"}', '$'
+    COLUMNS (js1 jsonb_test_domain path '$.d1' DEFAULT 'foo2'::jsonb_test_domain ON ERROR));
+ js1  
+------
+ foo2
+(1 row)
+
 -- "formatted" columns
 SELECT *
 FROM json_table_test vals
diff --git a/src/test/regress/expected/sqljson_queryfuncs.out b/src/test/regress/expected/sqljson_queryfuncs.out
index 9e86b0da..375b712a 100644
--- a/src/test/regress/expected/sqljson_queryfuncs.out
+++ b/src/test/regress/expected/sqljson_queryfuncs.out
@@ -1213,15 +1213,37 @@ ERROR:  DEFAULT expression must not contain column references
 LINE 1: SELECT JSON_QUERY(js, '$'  RETURNING int DEFAULT b + 1 ON ER...
                                                          ^
 SELECT JSON_QUERY(js, '$'  RETURNING int DEFAULT sum(1) over() ON ERROR) FROM test_jsonb_mutability;
-ERROR:  can only specify a constant, non-aggregate function, or operator expression for DEFAULT
+ERROR:  can only specify a constant, non-aggregate function, or operator expression or cast expression for DEFAULT
 LINE 1: SELECT JSON_QUERY(js, '$'  RETURNING int DEFAULT sum(1) over...
                                                          ^
 SELECT JSON_QUERY(js, '$'  RETURNING int DEFAULT (SELECT 1) ON ERROR) FROM test_jsonb_mutability;
-ERROR:  can only specify a constant, non-aggregate function, or operator expression for DEFAULT
+ERROR:  can only specify a constant, non-aggregate function, or operator expression or cast expression for DEFAULT
 LINE 1: SELECT JSON_QUERY(js, '$'  RETURNING int DEFAULT (SELECT 1) ...
                                                          ^
 DROP TABLE test_jsonb_mutability;
 DROP FUNCTION ret_setint;
+CREATE DOMAIN jsonb_test_domain AS text CHECK (value <> 'foo');
+SELECT JSON_VALUE(jsonb '{"d1": "H"}', '$.a2' returning jsonb_test_domain DEFAULT 'foo'::jsonb_test_domain ON ERROR);
+ERROR:  value for domain jsonb_test_domain violates check constraint "jsonb_test_domain_check"
+SELECT JSON_VALUE(jsonb '{"d1": "H"}', '$.a2' returning jsonb_test_domain DEFAULT 'foo1'::jsonb_test_domain ON ERROR);
+ json_value 
+------------
+ foo1
+(1 row)
+
+SELECT JSON_VALUE(jsonb '{"d1": "H"}', '$.a2' returning jsonb_test_domain DEFAULT '"foo1"'::jsonb::text ON ERROR);
+ json_value 
+------------
+ "foo1"
+(1 row)
+
+SELECT JSON_VALUE(jsonb '{"d1": "foo"}', '$.a2' returning jsonb_test_domain DEFAULT 'foo1'::jsonb_test_domain ON ERROR);
+ json_value 
+------------
+ foo1
+(1 row)
+
+drop domain jsonb_test_domain;
 -- Extension: non-constant JSON path
 SELECT JSON_EXISTS(jsonb '{"a": 123}', '$' || '.' || 'a');
  json_exists 
diff --git a/src/test/regress/sql/sqljson_jsontable.sql b/src/test/regress/sql/sqljson_jsontable.sql
index 3752ccc4..3c00734f 100644
--- a/src/test/regress/sql/sqljson_jsontable.sql
+++ b/src/test/regress/sql/sqljson_jsontable.sql
@@ -63,6 +63,19 @@ FROM json_table_test vals
 	) jt
 	ON true;
 
+--check default expression
+SELECT * FROM JSON_TABLE(jsonb '{"d1": "H"}', '$'
+    COLUMNS (js1 jsonb_test_domain path '$.a2' DEFAULT '"foo1"'::jsonb::text ON ERROR));
+
+SELECT * FROM JSON_TABLE(jsonb '{"d1": "H"}', '$'
+    COLUMNS (js1 jsonb_test_domain path '$.a2' DEFAULT 'foo'::jsonb_test_domain ON ERROR));
+
+SELECT * FROM JSON_TABLE(jsonb '{"d1": "H"}', '$'
+    COLUMNS (js1 jsonb_test_domain path '$.a2' DEFAULT 'foo1'::jsonb_test_domain ON ERROR));
+
+SELECT * FROM JSON_TABLE(jsonb '{"d1": "foo"}', '$'
+    COLUMNS (js1 jsonb_test_domain path '$.d1' DEFAULT 'foo2'::jsonb_test_domain ON ERROR));
+
 -- "formatted" columns
 SELECT *
 FROM json_table_test vals
diff --git a/src/test/regress/sql/sqljson_queryfuncs.sql b/src/test/regress/sql/sqljson_queryfuncs.sql
index ec330d3b..a8cde83d 100644
--- a/src/test/regress/sql/sqljson_queryfuncs.sql
+++ b/src/test/regress/sql/sqljson_queryfuncs.sql
@@ -411,6 +411,13 @@ SELECT JSON_QUERY(js, '$'  RETURNING int DEFAULT (SELECT 1) ON ERROR) FROM test_
 DROP TABLE test_jsonb_mutability;
 DROP FUNCTION ret_setint;
 
+CREATE DOMAIN jsonb_test_domain AS text CHECK (value <> 'foo');
+SELECT JSON_VALUE(jsonb '{"d1": "H"}', '$.a2' returning jsonb_test_domain DEFAULT 'foo'::jsonb_test_domain ON ERROR);
+SELECT JSON_VALUE(jsonb '{"d1": "H"}', '$.a2' returning jsonb_test_domain DEFAULT 'foo1'::jsonb_test_domain ON ERROR);
+SELECT JSON_VALUE(jsonb '{"d1": "H"}', '$.a2' returning jsonb_test_domain DEFAULT '"foo1"'::jsonb::text ON ERROR);
+SELECT JSON_VALUE(jsonb '{"d1": "foo"}', '$.a2' returning jsonb_test_domain DEFAULT 'foo1'::jsonb_test_domain ON ERROR);
+drop domain jsonb_test_domain;
+
 -- Extension: non-constant JSON path
 SELECT JSON_EXISTS(jsonb '{"a": 123}', '$' || '.' || 'a');
 SELECT JSON_VALUE(jsonb '{"a": 123}', '$' || '.' || 'a');

base-commit: d21d61b96f7a4d89e4b2e7cc9b9a1ec3f642fa12
-- 
2.34.1

From cbcd9c47926fade076fd474e3f43fa36cf79d0dc Mon Sep 17 00:00:00 2001
From: Amit Langote <amitlan@postgresql.org>
Date: Tue, 9 Apr 2024 20:31:30 +0900
Subject: [PATCH v4] SQL/JSON: Miscellaneous fixes and improvements

This addresses some post-commit review comments for commits 6185c973,
de3600452, and 9425c596a0, with the following changes:

* Improve some SQL/JSON query function error messages by mentioning
  the column name when available, such as when they are invoked as
  part of evaluating JSON_TABLE() columns.  To do so, a new field
  column_name is added to both JsonFuncExpr and JsonExpr that is only
  populated when creating those nodes when transforming the
  JSON_TABLE() columns' parser nodes.

* Reword SQL/JSON query function error messages for clarity.

* Fix JSON_TABLE() syntax documentation to use the term
  "path_expression" for JSON path expressions instead of
  "json_path_specification" to be consistent with the rest
  of the SQL/JSON functions.

* Fix a typo in the example code in JSON_TABLE() documentation.

* Fix some newly added comments in jsonpath.h.

* In JsonPathQuery(), add missing cast to int before printing an
  enum value.

Reported-by: Jian He <jian.universality@gmail.com>
Suggested-by: Jian He <jian.universality@gmail.com>
Discussion: https://postgr.es/m/CACJufxG_e0QLCgaELrr2ZNz7AxPeGCNKAORe3fHtFCQLsH4J4Q@mail.gmail.com
---
 doc/src/sgml/func.sgml                        |  9 ++--
 src/backend/executor/execExprInterp.c         | 42 ++++++++++-------
 src/backend/parser/parse_expr.c               |  1 +
 src/backend/parser/parse_jsontable.c          |  9 ++--
 src/backend/utils/adt/jsonpath_exec.c         | 47 ++++++++++++++-----
 src/include/nodes/parsenodes.h                |  2 +
 src/include/nodes/primnodes.h                 |  3 ++
 src/include/utils/jsonpath.h                  | 14 +++---
 .../regress/expected/sqljson_jsontable.out    | 10 ++--
 .../regress/expected/sqljson_queryfuncs.out   | 10 ++--
 src/test/regress/sql/sqljson_jsontable.sql    |  3 ++
 11 files changed, 94 insertions(+), 56 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 8dfb42ad4d..92a0f49e6a 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -18942,7 +18942,7 @@ where <replaceable class="parameter">json_table_column</replaceable> is:
         <optional> { ERROR | NULL | EMPTY { ARRAY | OBJECT } | DEFAULT <replaceable>expression</replaceable> } ON ERROR </optional>
   | <replaceable>name</replaceable> <replaceable>type</replaceable> EXISTS <optional> PATH <replaceable>path_expression</replaceable> </optional>
         <optional> { ERROR | TRUE | FALSE | UNKNOWN } ON ERROR </optional>
-  | NESTED <optional> PATH </optional> <replaceable>json_path_specification</replaceable> <optional> AS <replaceable>json_path_name</replaceable> </optional> COLUMNS ( <replaceable>json_table_column</replaceable> <optional>, ...</optional> )
+  | NESTED <optional> PATH </optional> <replaceable>path_expression</replaceable> <optional> AS <replaceable>json_path_name</replaceable> </optional> COLUMNS ( <replaceable>json_table_column</replaceable> <optional>, ...</optional> )
 </synopsis>
 
   <para>
@@ -19083,7 +19083,7 @@ where <replaceable class="parameter">json_table_column</replaceable> is:
 
    <varlistentry>
     <term>
-      <literal>NESTED <optional> PATH </optional></literal> <replaceable>json_path_specification</replaceable> <optional> <literal>AS</literal> <replaceable>json_path_name</replaceable> </optional>
+      <literal>NESTED <optional> PATH </optional></literal> <replaceable>path_expression</replaceable> <optional> <literal>AS</literal> <replaceable>json_path_name</replaceable> </optional>
           <literal>COLUMNS</literal> ( <replaceable>json_table_column</replaceable> <optional>, ...</optional> )
     </term>
     <listitem>
@@ -19315,8 +19315,9 @@ SELECT * FROM JSON_TABLE (
      "books":
       [{"name": "Mystery", "authors": [{"name": "Brown Dan"}]},
        {"name": "Wonder", "authors": [{"name": "Jun Murakami"}, {"name":"Craig Doe"}]}]
-}}'::json, '$.favs[*]'
-COLUMNS (user_id FOR ORDINALITY,
+}}'::json, '$.favorites[*]'
+COLUMNS (
+  user_id FOR ORDINALITY,
   NESTED '$.movies[*]'
     COLUMNS (
     movie_id FOR ORDINALITY,
diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index 41af28cb1e..580c897144 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -4312,7 +4312,8 @@ ExecEvalJsonExprPath(ExprState *state, ExprEvalStep *op,
 		case JSON_QUERY_OP:
 			*op->resvalue = JsonPathQuery(item, path, jsexpr->wrapper, &empty,
 										  !throw_error ? &error : NULL,
-										  jsestate->args);
+										  jsestate->args,
+										  jsexpr->column_name);
 
 			*op->resnull = (DatumGetPointer(*op->resvalue) == NULL);
 
@@ -4337,7 +4338,8 @@ ExecEvalJsonExprPath(ExprState *state, ExprEvalStep *op,
 			{
 				JsonbValue *jbv = JsonPathValue(item, path, &empty,
 												!throw_error ? &error : NULL,
-												jsestate->args);
+												jsestate->args,
+												jsexpr->column_name);
 
 				if (jbv == NULL)
 				{
@@ -4409,28 +4411,32 @@ ExecEvalJsonExprPath(ExprState *state, ExprEvalStep *op,
 	{
 		if (jsexpr->on_empty)
 		{
-			if (jsexpr->on_empty->btype == JSON_BEHAVIOR_ERROR)
-				ereport(ERROR,
-						errcode(ERRCODE_NO_SQL_JSON_ITEM),
-						errmsg("no SQL/JSON item"));
-			else
+			if (jsexpr->on_empty->btype != JSON_BEHAVIOR_ERROR)
+			{
 				jsestate->empty.value = BoolGetDatum(true);
+				Assert(jsestate->jump_empty >= 0);
+				return jsestate->jump_empty;
+			}
+		}
+		else if (jsexpr->on_error->btype != JSON_BEHAVIOR_ERROR)
+		{
+			jsestate->error.value = BoolGetDatum(true);
 
-			Assert(jsestate->jump_empty >= 0);
-			return jsestate->jump_empty;
+			*op->resvalue = (Datum) 0;
+			*op->resnull = true;
+			Assert(!throw_error && jsestate->jump_error >= 0);
+			return jsestate->jump_error;
 		}
-		else if (jsexpr->on_error->btype == JSON_BEHAVIOR_ERROR)
+
+		if (jsexpr->column_name)
 			ereport(ERROR,
 					errcode(ERRCODE_NO_SQL_JSON_ITEM),
-					errmsg("no SQL/JSON item"));
+					errmsg("no SQL/JSON item found for column \"%s\"",
+						   jsexpr->column_name));
 		else
-			jsestate->error.value = BoolGetDatum(true);
-
-		*op->resvalue = (Datum) 0;
-		*op->resnull = true;
-
-		Assert(!throw_error && jsestate->jump_error >= 0);
-		return jsestate->jump_error;
+			ereport(ERROR,
+					errcode(ERRCODE_NO_SQL_JSON_ITEM),
+					errmsg("no SQL/JSON item"));
 	}
 
 	/*
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 4c98d7a046..34ac17868b 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -4311,6 +4311,7 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
 	jsexpr = makeNode(JsonExpr);
 	jsexpr->location = func->location;
 	jsexpr->op = func->op;
+	jsexpr->column_name = func->column_name;
 
 	/*
 	 * jsonpath machinery can only handle jsonb documents, so coerce the input
diff --git a/src/backend/parser/parse_jsontable.c b/src/backend/parser/parse_jsontable.c
index 99d3101f6b..70b00a45f0 100644
--- a/src/backend/parser/parse_jsontable.c
+++ b/src/backend/parser/parse_jsontable.c
@@ -402,12 +402,6 @@ transformJsonTableColumn(JsonTableColumn *jtc, Node *contextItemExpr,
 	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)
@@ -415,6 +409,9 @@ transformJsonTableColumn(JsonTableColumn *jtc, Node *contextItemExpr,
 	else
 		jfexpr->op = JSON_QUERY_OP;
 
+	/* Pass the column name so any runtime JsonExpr errors can print it. */
+	jfexpr->column_name = pstrdup(jtc->name);
+
 	jfexpr->context_item = makeJsonValueExpr((Expr *) contextItemExpr, NULL,
 											 makeJsonFormat(JS_FORMAT_DEFAULT,
 															JS_ENC_DEFAULT,
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index 103572ed93..4daf1a68d9 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -3899,7 +3899,8 @@ JsonPathExists(Datum jb, JsonPath *jp, bool *error, List *vars)
  */
 Datum
 JsonPathQuery(Datum jb, JsonPath *jp, JsonWrapper wrapper, bool *empty,
-			  bool *error, List *vars)
+			  bool *error, List *vars,
+			  const char *column_name)
 {
 	JsonbValue *singleton;
 	bool		wrap;
@@ -3934,7 +3935,7 @@ JsonPathQuery(Datum jb, JsonPath *jp, JsonWrapper wrapper, bool *empty,
 			 JsonContainerIsScalar(singleton->val.binary.data));
 	else
 	{
-		elog(ERROR, "unrecognized json wrapper %d", wrapper);
+		elog(ERROR, "unrecognized json wrapper %d", (int) wrapper);
 		wrap = false;
 	}
 
@@ -3950,10 +3951,17 @@ JsonPathQuery(Datum jb, JsonPath *jp, JsonWrapper wrapper, bool *empty,
 			return (Datum) 0;
 		}
 
-		ereport(ERROR,
-				(errcode(ERRCODE_MORE_THAN_ONE_SQL_JSON_ITEM),
-				 errmsg("JSON path expression in JSON_QUERY should return singleton item without wrapper"),
-				 errhint("Use WITH WRAPPER clause to wrap SQL/JSON item sequence into array.")));
+		if (column_name)
+			ereport(ERROR,
+					(errcode(ERRCODE_MORE_THAN_ONE_SQL_JSON_ITEM),
+					 errmsg("JSON path expression for column \"%s\" should return single item without wrapper",
+							column_name),
+					 errhint("Use WITH WRAPPER clause to wrap SQL/JSON items into array.")));
+		else
+			ereport(ERROR,
+					(errcode(ERRCODE_MORE_THAN_ONE_SQL_JSON_ITEM),
+					 errmsg("JSON path expression in JSON_QUERY should return single item without wrapper"),
+					 errhint("Use WITH WRAPPER clause to wrap SQL/JSON items into array.")));
 	}
 
 	if (singleton)
@@ -3970,7 +3978,8 @@ JsonPathQuery(Datum jb, JsonPath *jp, JsonWrapper wrapper, bool *empty,
  * *error to true.  *empty is set to true if no match is found.
  */
 JsonbValue *
-JsonPathValue(Datum jb, JsonPath *jp, bool *empty, bool *error, List *vars)
+JsonPathValue(Datum jb, JsonPath *jp, bool *empty, bool *error, List *vars,
+			  const char *column_name)
 {
 	JsonbValue *res;
 	JsonValueList found = {0};
@@ -4006,9 +4015,15 @@ JsonPathValue(Datum jb, JsonPath *jp, bool *empty, bool *error, List *vars)
 			return NULL;
 		}
 
-		ereport(ERROR,
-				(errcode(ERRCODE_MORE_THAN_ONE_SQL_JSON_ITEM),
-				 errmsg("JSON path expression in JSON_VALUE should return singleton scalar item")));
+		if (column_name)
+			ereport(ERROR,
+					(errcode(ERRCODE_MORE_THAN_ONE_SQL_JSON_ITEM),
+					 errmsg("JSON path expression for column \"%s\" should return single scalar item",
+							column_name)));
+		else
+			ereport(ERROR,
+					(errcode(ERRCODE_MORE_THAN_ONE_SQL_JSON_ITEM),
+					 errmsg("JSON path expression in JSON_VALUE should return single scalar item")));
 	}
 
 	res = JsonValueListHead(&found);
@@ -4024,9 +4039,15 @@ JsonPathValue(Datum jb, JsonPath *jp, bool *empty, bool *error, List *vars)
 			return NULL;
 		}
 
-		ereport(ERROR,
-				(errcode(ERRCODE_SQL_JSON_SCALAR_REQUIRED),
-				 errmsg("JSON path expression in JSON_VALUE should return singleton scalar item")));
+		if (column_name)
+			ereport(ERROR,
+					(errcode(ERRCODE_SQL_JSON_SCALAR_REQUIRED),
+					 errmsg("JSON path expression for column \"%s\" should return single scalar item",
+							column_name)));
+		else
+			ereport(ERROR,
+					(errcode(ERRCODE_SQL_JSON_SCALAR_REQUIRED),
+					 errmsg("JSON path expression in JSON_VALUE should return single scalar item")));
 	}
 
 	if (res->type == jbvNull)
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index f763f790b1..c0ff75e643 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1791,6 +1791,8 @@ typedef struct JsonFuncExpr
 {
 	NodeTag		type;
 	JsonExprOp	op;				/* expression type */
+	char	   *column_name;	/* JSON_TABLE() column name or NULL if this is
+								 * not for a JSON_TABLE() */
 	JsonValueExpr *context_item;	/* context item expression */
 	Node	   *pathspec;		/* JSON path specification expression */
 	List	   *passing;		/* list of PASSING clause arguments, if any */
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index dafe93a4c9..9b662b8dd2 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -1782,6 +1782,9 @@ typedef struct JsonExpr
 
 	JsonExprOp	op;
 
+	char	   *column_name;	/* JSON_TABLE() column name or NULL if this is
+								 * not for a JSON_TABLE() */
+
 	/* jsonb-valued expression to query */
 	Node	   *formatted_expr;
 
diff --git a/src/include/utils/jsonpath.h b/src/include/utils/jsonpath.h
index 4d3964488d..69c180c2e2 100644
--- a/src/include/utils/jsonpath.h
+++ b/src/include/utils/jsonpath.h
@@ -281,12 +281,9 @@ extern JsonPathParseResult *parsejsonpath(const char *str, int len,
 extern bool jspConvertRegexFlags(uint32 xflags, int *result,
 								 struct Node *escontext);
 
-
 /*
- * Evaluation of jsonpath
+ * Struct for details about external variables passed into jsonpath executor
  */
-
-/* External variable passed into jsonpath. */
 typedef struct JsonPathVariable
 {
 	char	   *name;
@@ -297,13 +294,16 @@ typedef struct JsonPathVariable
 } JsonPathVariable;
 
 
-/* SQL/JSON item */
+/* SQL/JSON query functions */
 extern bool JsonPathExists(Datum jb, JsonPath *path, bool *error, List *vars);
 extern Datum JsonPathQuery(Datum jb, JsonPath *jp, JsonWrapper wrapper,
-						   bool *empty, bool *error, List *vars);
+						   bool *empty, bool *error, List *vars,
+						   const char *column_name);
 extern JsonbValue *JsonPathValue(Datum jb, JsonPath *jp, bool *empty,
-								 bool *error, List *vars);
+								 bool *error, List *vars,
+								 const char *column_name);
 
+/* For JSON_TABLE() */
 extern PGDLLIMPORT const TableFuncRoutine JsonbTableRoutine;
 
 #endif
diff --git a/src/test/regress/expected/sqljson_jsontable.out b/src/test/regress/expected/sqljson_jsontable.out
index a00eec8a6f..d65fa7fe69 100644
--- a/src/test/regress/expected/sqljson_jsontable.out
+++ b/src/test/regress/expected/sqljson_jsontable.out
@@ -492,11 +492,11 @@ FROM
 		ON true;
 ERROR:  invalid input syntax for type integer: "err"
 SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH '$.a' ERROR ON EMPTY)) jt;
-ERROR:  no SQL/JSON item
+ERROR:  no SQL/JSON item found for column "a"
 SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH 'strict $.a' ERROR ON ERROR) ERROR ON ERROR) jt;
 ERROR:  jsonpath member accessor can only be applied to an object
 SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH 'lax $.a' ERROR ON EMPTY) ERROR ON ERROR) jt;
-ERROR:  no SQL/JSON item
+ERROR:  no SQL/JSON item found for column "a"
 SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH '$'   DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt;
  a 
 ---
@@ -637,6 +637,10 @@ SELECT * FROM JSON_TABLE(jsonb '{"a": 123}', '$' || '.' || 'a' COLUMNS (foo int)
 ERROR:  only string constants are supported in JSON_TABLE path specification
 LINE 1: SELECT * FROM JSON_TABLE(jsonb '{"a": 123}', '$' || '.' || '...
                                                      ^
+-- JsonPathQuery() error message mentioning column name
+SELECT * FROM JSON_TABLE('{"a": [{"b": "1"}, {"b": "2"}]}', '$' COLUMNS (b json path '$.a[*].b' ERROR ON ERROR));
+ERROR:  JSON path expression for column "b" should return single item without wrapper
+HINT:  Use WITH WRAPPER clause to wrap SQL/JSON items into array.
 -- JSON_TABLE: nested paths
 -- Duplicate path names
 SELECT * FROM JSON_TABLE(
@@ -849,7 +853,7 @@ SELECT sub.* FROM s,
 		xx int path '$.c',
 		NESTED PATH '$.a.za[1]' columns (NESTED PATH '$.z21[*]' COLUMNS (z21 int path '$?(@ >= $"x")' ERROR ON ERROR))
 	)) sub;
-ERROR:  no SQL/JSON item
+ERROR:  no SQL/JSON item found for column "z21"
 -- Parent columns xx1, xx appear before NESTED ones
 SELECT sub.* FROM s,
 	(VALUES (23)) x(x), generate_series(13, 13) y,
diff --git a/src/test/regress/expected/sqljson_queryfuncs.out b/src/test/regress/expected/sqljson_queryfuncs.out
index 9e86b0da10..6c5f1b3ecd 100644
--- a/src/test/regress/expected/sqljson_queryfuncs.out
+++ b/src/test/regress/expected/sqljson_queryfuncs.out
@@ -339,7 +339,7 @@ SELECT JSON_VALUE(jsonb '[]', '$');
 (1 row)
 
 SELECT JSON_VALUE(jsonb '[]', '$' ERROR ON ERROR);
-ERROR:  JSON path expression in JSON_VALUE should return singleton scalar item
+ERROR:  JSON path expression in JSON_VALUE should return single scalar item
 SELECT JSON_VALUE(jsonb '{}', '$');
  json_value 
 ------------
@@ -347,7 +347,7 @@ SELECT JSON_VALUE(jsonb '{}', '$');
 (1 row)
 
 SELECT JSON_VALUE(jsonb '{}', '$' ERROR ON ERROR);
-ERROR:  JSON path expression in JSON_VALUE should return singleton scalar item
+ERROR:  JSON path expression in JSON_VALUE should return single scalar item
 SELECT JSON_VALUE(jsonb '1', '$.a');
  json_value 
 ------------
@@ -399,7 +399,7 @@ SELECT JSON_VALUE(jsonb '1', 'lax $.a' DEFAULT '2' ON EMPTY DEFAULT '3' ON ERROR
 SELECT JSON_VALUE(jsonb '1', 'lax $.a' ERROR ON EMPTY DEFAULT '3' ON ERROR);
 ERROR:  no SQL/JSON item
 SELECT JSON_VALUE(jsonb '[1,2]', '$[*]' ERROR ON ERROR);
-ERROR:  JSON path expression in JSON_VALUE should return singleton scalar item
+ERROR:  JSON path expression in JSON_VALUE should return single scalar item
 SELECT JSON_VALUE(jsonb '[1,2]', '$[*]' DEFAULT '0' ON ERROR);
  json_value 
 ------------
@@ -776,8 +776,8 @@ ERROR:  no SQL/JSON item
 SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON ERROR);
 ERROR:  no SQL/JSON item
 SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' ERROR ON ERROR);
-ERROR:  JSON path expression in JSON_QUERY should return singleton item without wrapper
-HINT:  Use WITH WRAPPER clause to wrap SQL/JSON item sequence into array.
+ERROR:  JSON path expression in JSON_QUERY should return single item without wrapper
+HINT:  Use WITH WRAPPER clause to wrap SQL/JSON items into array.
 SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' DEFAULT '"empty"' ON ERROR);
  json_query 
 ------------
diff --git a/src/test/regress/sql/sqljson_jsontable.sql b/src/test/regress/sql/sqljson_jsontable.sql
index 3752ccc446..29c0c6ba52 100644
--- a/src/test/regress/sql/sqljson_jsontable.sql
+++ b/src/test/regress/sql/sqljson_jsontable.sql
@@ -290,6 +290,9 @@ FROM JSON_TABLE(
 -- Should fail (not supported)
 SELECT * FROM JSON_TABLE(jsonb '{"a": 123}', '$' || '.' || 'a' COLUMNS (foo int));
 
+-- JsonPathQuery() error message mentioning column name
+SELECT * FROM JSON_TABLE('{"a": [{"b": "1"}, {"b": "2"}]}', '$' COLUMNS (b json path '$.a[*].b' ERROR ON ERROR));
+
 -- JSON_TABLE: nested paths
 
 -- Duplicate path names
-- 
2.43.0

From 80563942590b94a23d73612a510cacd15541ccf9 Mon Sep 17 00:00:00 2001
From: Amit Langote <amitlan@postgresql.org>
Date: Wed, 17 Apr 2024 12:00:42 +0900
Subject: [PATCH v5 2/3] SQL/JSON: Miscellaneous fixes and improvements

This addresses some post-commit review comments for commits 6185c973,
de3600452, and 9425c596a0, with the following changes:

* Fix JSON_TABLE() syntax documentation to use the term
  "path_expression" for JSON path expressions instead of
  "json_path_specification" to be consistent with the rest
  of the SQL/JSON functions.

* Fix a typo in the example code in JSON_TABLE() documentation.

* Rewrite some newly added comments in jsonpath.h.

* In JsonPathQuery(), add missing cast to int before printing an
  enum value.

Reported-by: Jian He <jian.universality@gmail.com>
Discussion: https://postgr.es/m/CACJufxG_e0QLCgaELrr2ZNz7AxPeGCNKAORe3fHtFCQLsH4J4Q@mail.gmail.com
---
 doc/src/sgml/func.sgml                | 9 +++++----
 src/backend/utils/adt/jsonpath_exec.c | 2 +-
 src/include/utils/jsonpath.h          | 8 +++-----
 3 files changed, 9 insertions(+), 10 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 8dfb42ad4d..92a0f49e6a 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -18942,7 +18942,7 @@ where <replaceable class="parameter">json_table_column</replaceable> is:
         <optional> { ERROR | NULL | EMPTY { ARRAY | OBJECT } | DEFAULT <replaceable>expression</replaceable> } ON ERROR </optional>
   | <replaceable>name</replaceable> <replaceable>type</replaceable> EXISTS <optional> PATH <replaceable>path_expression</replaceable> </optional>
         <optional> { ERROR | TRUE | FALSE | UNKNOWN } ON ERROR </optional>
-  | NESTED <optional> PATH </optional> <replaceable>json_path_specification</replaceable> <optional> AS <replaceable>json_path_name</replaceable> </optional> COLUMNS ( <replaceable>json_table_column</replaceable> <optional>, ...</optional> )
+  | NESTED <optional> PATH </optional> <replaceable>path_expression</replaceable> <optional> AS <replaceable>json_path_name</replaceable> </optional> COLUMNS ( <replaceable>json_table_column</replaceable> <optional>, ...</optional> )
 </synopsis>
 
   <para>
@@ -19083,7 +19083,7 @@ where <replaceable class="parameter">json_table_column</replaceable> is:
 
    <varlistentry>
     <term>
-      <literal>NESTED <optional> PATH </optional></literal> <replaceable>json_path_specification</replaceable> <optional> <literal>AS</literal> <replaceable>json_path_name</replaceable> </optional>
+      <literal>NESTED <optional> PATH </optional></literal> <replaceable>path_expression</replaceable> <optional> <literal>AS</literal> <replaceable>json_path_name</replaceable> </optional>
           <literal>COLUMNS</literal> ( <replaceable>json_table_column</replaceable> <optional>, ...</optional> )
     </term>
     <listitem>
@@ -19315,8 +19315,9 @@ SELECT * FROM JSON_TABLE (
      "books":
       [{"name": "Mystery", "authors": [{"name": "Brown Dan"}]},
        {"name": "Wonder", "authors": [{"name": "Jun Murakami"}, {"name":"Craig Doe"}]}]
-}}'::json, '$.favs[*]'
-COLUMNS (user_id FOR ORDINALITY,
+}}'::json, '$.favorites[*]'
+COLUMNS (
+  user_id FOR ORDINALITY,
   NESTED '$.movies[*]'
     COLUMNS (
     movie_id FOR ORDINALITY,
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index e74dc1b2d4..4daf1a68d9 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -3935,7 +3935,7 @@ JsonPathQuery(Datum jb, JsonPath *jp, JsonWrapper wrapper, bool *empty,
 			 JsonContainerIsScalar(singleton->val.binary.data));
 	else
 	{
-		elog(ERROR, "unrecognized json wrapper %d", wrapper);
+		elog(ERROR, "unrecognized json wrapper %d", (int) wrapper);
 		wrap = false;
 	}
 
diff --git a/src/include/utils/jsonpath.h b/src/include/utils/jsonpath.h
index 0bcc1ac569..69c180c2e2 100644
--- a/src/include/utils/jsonpath.h
+++ b/src/include/utils/jsonpath.h
@@ -281,12 +281,9 @@ extern JsonPathParseResult *parsejsonpath(const char *str, int len,
 extern bool jspConvertRegexFlags(uint32 xflags, int *result,
 								 struct Node *escontext);
 
-
 /*
- * Evaluation of jsonpath
+ * Struct for details about external variables passed into jsonpath executor
  */
-
-/* External variable passed into jsonpath. */
 typedef struct JsonPathVariable
 {
 	char	   *name;
@@ -297,7 +294,7 @@ typedef struct JsonPathVariable
 } JsonPathVariable;
 
 
-/* SQL/JSON item */
+/* SQL/JSON query functions */
 extern bool JsonPathExists(Datum jb, JsonPath *path, bool *error, List *vars);
 extern Datum JsonPathQuery(Datum jb, JsonPath *jp, JsonWrapper wrapper,
 						   bool *empty, bool *error, List *vars,
@@ -306,6 +303,7 @@ extern JsonbValue *JsonPathValue(Datum jb, JsonPath *jp, bool *empty,
 								 bool *error, List *vars,
 								 const char *column_name);
 
+/* For JSON_TABLE() */
 extern PGDLLIMPORT const TableFuncRoutine JsonbTableRoutine;
 
 #endif
-- 
2.43.0

From 9662679578717545c8d54efca77b179670110316 Mon Sep 17 00:00:00 2001
From: Amit Langote <amitlan@postgresql.org>
Date: Wed, 17 Apr 2024 11:40:35 +0900
Subject: [PATCH v5 1/3] SQL/JSON: Improve some error messages

This improves some SQL/JSON query function error messages by
mentioning column name when available, such as when they are invoked
as part of evaluating JSON_TABLE() columns.  To do so, a new field
column_name is added to both JsonFuncExpr and JsonExpr that is only
populated when creating those nodes when transforming the JSON_TABLE()
columns' parser nodes.

While at it, the error messages are reworded for clarity.

Reported-by: Jian He <jian.universality@gmail.com>
Suggested-by: Jian He <jian.universality@gmail.com>
Discussion: https://postgr.es/m/CACJufxG_e0QLCgaELrr2ZNz7AxPeGCNKAORe3fHtFCQLsH4J4Q@mail.gmail.com
---
 src/backend/executor/execExprInterp.c         | 43 ++++++++++--------
 src/backend/parser/parse_expr.c               |  1 +
 src/backend/parser/parse_jsontable.c          |  9 ++--
 src/backend/utils/adt/jsonpath_exec.c         | 45 ++++++++++++++-----
 src/include/nodes/parsenodes.h                |  2 +
 src/include/nodes/primnodes.h                 |  3 ++
 src/include/utils/jsonpath.h                  |  6 ++-
 .../regress/expected/sqljson_jsontable.out    | 10 +++--
 .../regress/expected/sqljson_queryfuncs.out   | 30 ++++++-------
 src/test/regress/sql/sqljson_jsontable.sql    |  3 ++
 10 files changed, 95 insertions(+), 57 deletions(-)

diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index 41af28cb1e..852186312c 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -4312,7 +4312,8 @@ ExecEvalJsonExprPath(ExprState *state, ExprEvalStep *op,
 		case JSON_QUERY_OP:
 			*op->resvalue = JsonPathQuery(item, path, jsexpr->wrapper, &empty,
 										  !throw_error ? &error : NULL,
-										  jsestate->args);
+										  jsestate->args,
+										  jsexpr->column_name);
 
 			*op->resnull = (DatumGetPointer(*op->resvalue) == NULL);
 
@@ -4337,7 +4338,8 @@ ExecEvalJsonExprPath(ExprState *state, ExprEvalStep *op,
 			{
 				JsonbValue *jbv = JsonPathValue(item, path, &empty,
 												!throw_error ? &error : NULL,
-												jsestate->args);
+												jsestate->args,
+												jsexpr->column_name);
 
 				if (jbv == NULL)
 				{
@@ -4407,30 +4409,33 @@ ExecEvalJsonExprPath(ExprState *state, ExprEvalStep *op,
 	/* Handle ON EMPTY. */
 	if (empty)
 	{
+		*op->resvalue = (Datum) 0;
+		*op->resnull = true;
 		if (jsexpr->on_empty)
 		{
-			if (jsexpr->on_empty->btype == JSON_BEHAVIOR_ERROR)
-				ereport(ERROR,
-						errcode(ERRCODE_NO_SQL_JSON_ITEM),
-						errmsg("no SQL/JSON item"));
-			else
+			if (jsexpr->on_empty->btype != JSON_BEHAVIOR_ERROR)
+			{
 				jsestate->empty.value = BoolGetDatum(true);
-
-			Assert(jsestate->jump_empty >= 0);
-			return jsestate->jump_empty;
+				Assert(jsestate->jump_empty >= 0);
+				return jsestate->jump_empty;
+			}
+		}
+		else if (jsexpr->on_error->btype != JSON_BEHAVIOR_ERROR)
+		{
+			jsestate->error.value = BoolGetDatum(true);
+			Assert(!throw_error && jsestate->jump_error >= 0);
+			return jsestate->jump_error;
 		}
-		else if (jsexpr->on_error->btype == JSON_BEHAVIOR_ERROR)
+
+		if (jsexpr->column_name)
 			ereport(ERROR,
 					errcode(ERRCODE_NO_SQL_JSON_ITEM),
-					errmsg("no SQL/JSON item"));
+					errmsg("no SQL/JSON item found for specified path of column \"%s\"",
+						   jsexpr->column_name));
 		else
-			jsestate->error.value = BoolGetDatum(true);
-
-		*op->resvalue = (Datum) 0;
-		*op->resnull = true;
-
-		Assert(!throw_error && jsestate->jump_error >= 0);
-		return jsestate->jump_error;
+			ereport(ERROR,
+					errcode(ERRCODE_NO_SQL_JSON_ITEM),
+					errmsg("no SQL/JSON item found for specified path"));
 	}
 
 	/*
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 4c98d7a046..34ac17868b 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -4311,6 +4311,7 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
 	jsexpr = makeNode(JsonExpr);
 	jsexpr->location = func->location;
 	jsexpr->op = func->op;
+	jsexpr->column_name = func->column_name;
 
 	/*
 	 * jsonpath machinery can only handle jsonb documents, so coerce the input
diff --git a/src/backend/parser/parse_jsontable.c b/src/backend/parser/parse_jsontable.c
index 99d3101f6b..70b00a45f0 100644
--- a/src/backend/parser/parse_jsontable.c
+++ b/src/backend/parser/parse_jsontable.c
@@ -402,12 +402,6 @@ transformJsonTableColumn(JsonTableColumn *jtc, Node *contextItemExpr,
 	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)
@@ -415,6 +409,9 @@ transformJsonTableColumn(JsonTableColumn *jtc, Node *contextItemExpr,
 	else
 		jfexpr->op = JSON_QUERY_OP;
 
+	/* Pass the column name so any runtime JsonExpr errors can print it. */
+	jfexpr->column_name = pstrdup(jtc->name);
+
 	jfexpr->context_item = makeJsonValueExpr((Expr *) contextItemExpr, NULL,
 											 makeJsonFormat(JS_FORMAT_DEFAULT,
 															JS_ENC_DEFAULT,
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index 103572ed93..e74dc1b2d4 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -3899,7 +3899,8 @@ JsonPathExists(Datum jb, JsonPath *jp, bool *error, List *vars)
  */
 Datum
 JsonPathQuery(Datum jb, JsonPath *jp, JsonWrapper wrapper, bool *empty,
-			  bool *error, List *vars)
+			  bool *error, List *vars,
+			  const char *column_name)
 {
 	JsonbValue *singleton;
 	bool		wrap;
@@ -3950,10 +3951,17 @@ JsonPathQuery(Datum jb, JsonPath *jp, JsonWrapper wrapper, bool *empty,
 			return (Datum) 0;
 		}
 
-		ereport(ERROR,
-				(errcode(ERRCODE_MORE_THAN_ONE_SQL_JSON_ITEM),
-				 errmsg("JSON path expression in JSON_QUERY should return singleton item without wrapper"),
-				 errhint("Use WITH WRAPPER clause to wrap SQL/JSON item sequence into array.")));
+		if (column_name)
+			ereport(ERROR,
+					(errcode(ERRCODE_MORE_THAN_ONE_SQL_JSON_ITEM),
+					 errmsg("JSON path expression for column \"%s\" should return single item without wrapper",
+							column_name),
+					 errhint("Use WITH WRAPPER clause to wrap SQL/JSON items into array.")));
+		else
+			ereport(ERROR,
+					(errcode(ERRCODE_MORE_THAN_ONE_SQL_JSON_ITEM),
+					 errmsg("JSON path expression in JSON_QUERY should return single item without wrapper"),
+					 errhint("Use WITH WRAPPER clause to wrap SQL/JSON items into array.")));
 	}
 
 	if (singleton)
@@ -3970,7 +3978,8 @@ JsonPathQuery(Datum jb, JsonPath *jp, JsonWrapper wrapper, bool *empty,
  * *error to true.  *empty is set to true if no match is found.
  */
 JsonbValue *
-JsonPathValue(Datum jb, JsonPath *jp, bool *empty, bool *error, List *vars)
+JsonPathValue(Datum jb, JsonPath *jp, bool *empty, bool *error, List *vars,
+			  const char *column_name)
 {
 	JsonbValue *res;
 	JsonValueList found = {0};
@@ -4006,9 +4015,15 @@ JsonPathValue(Datum jb, JsonPath *jp, bool *empty, bool *error, List *vars)
 			return NULL;
 		}
 
-		ereport(ERROR,
-				(errcode(ERRCODE_MORE_THAN_ONE_SQL_JSON_ITEM),
-				 errmsg("JSON path expression in JSON_VALUE should return singleton scalar item")));
+		if (column_name)
+			ereport(ERROR,
+					(errcode(ERRCODE_MORE_THAN_ONE_SQL_JSON_ITEM),
+					 errmsg("JSON path expression for column \"%s\" should return single scalar item",
+							column_name)));
+		else
+			ereport(ERROR,
+					(errcode(ERRCODE_MORE_THAN_ONE_SQL_JSON_ITEM),
+					 errmsg("JSON path expression in JSON_VALUE should return single scalar item")));
 	}
 
 	res = JsonValueListHead(&found);
@@ -4024,9 +4039,15 @@ JsonPathValue(Datum jb, JsonPath *jp, bool *empty, bool *error, List *vars)
 			return NULL;
 		}
 
-		ereport(ERROR,
-				(errcode(ERRCODE_SQL_JSON_SCALAR_REQUIRED),
-				 errmsg("JSON path expression in JSON_VALUE should return singleton scalar item")));
+		if (column_name)
+			ereport(ERROR,
+					(errcode(ERRCODE_SQL_JSON_SCALAR_REQUIRED),
+					 errmsg("JSON path expression for column \"%s\" should return single scalar item",
+							column_name)));
+		else
+			ereport(ERROR,
+					(errcode(ERRCODE_SQL_JSON_SCALAR_REQUIRED),
+					 errmsg("JSON path expression in JSON_VALUE should return single scalar item")));
 	}
 
 	if (res->type == jbvNull)
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 0cab890843..c5f34efe27 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1791,6 +1791,8 @@ typedef struct JsonFuncExpr
 {
 	NodeTag		type;
 	JsonExprOp	op;				/* expression type */
+	char	   *column_name;	/* JSON_TABLE() column name or NULL if this is
+								 * not for a JSON_TABLE() */
 	JsonValueExpr *context_item;	/* context item expression */
 	Node	   *pathspec;		/* JSON path specification expression */
 	List	   *passing;		/* list of PASSING clause arguments, if any */
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 6b0172f427..0052c1f0ee 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -1782,6 +1782,9 @@ typedef struct JsonExpr
 
 	JsonExprOp	op;
 
+	char	   *column_name;	/* JSON_TABLE() column name or NULL if this is
+								 * not for a JSON_TABLE() */
+
 	/* jsonb-valued expression to query */
 	Node	   *formatted_expr;
 
diff --git a/src/include/utils/jsonpath.h b/src/include/utils/jsonpath.h
index 4d3964488d..0bcc1ac569 100644
--- a/src/include/utils/jsonpath.h
+++ b/src/include/utils/jsonpath.h
@@ -300,9 +300,11 @@ typedef struct JsonPathVariable
 /* SQL/JSON item */
 extern bool JsonPathExists(Datum jb, JsonPath *path, bool *error, List *vars);
 extern Datum JsonPathQuery(Datum jb, JsonPath *jp, JsonWrapper wrapper,
-						   bool *empty, bool *error, List *vars);
+						   bool *empty, bool *error, List *vars,
+						   const char *column_name);
 extern JsonbValue *JsonPathValue(Datum jb, JsonPath *jp, bool *empty,
-								 bool *error, List *vars);
+								 bool *error, List *vars,
+								 const char *column_name);
 
 extern PGDLLIMPORT const TableFuncRoutine JsonbTableRoutine;
 
diff --git a/src/test/regress/expected/sqljson_jsontable.out b/src/test/regress/expected/sqljson_jsontable.out
index a00eec8a6f..9eecd97f45 100644
--- a/src/test/regress/expected/sqljson_jsontable.out
+++ b/src/test/regress/expected/sqljson_jsontable.out
@@ -492,11 +492,11 @@ FROM
 		ON true;
 ERROR:  invalid input syntax for type integer: "err"
 SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH '$.a' ERROR ON EMPTY)) jt;
-ERROR:  no SQL/JSON item
+ERROR:  no SQL/JSON item found for specified path of column "a"
 SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH 'strict $.a' ERROR ON ERROR) ERROR ON ERROR) jt;
 ERROR:  jsonpath member accessor can only be applied to an object
 SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH 'lax $.a' ERROR ON EMPTY) ERROR ON ERROR) jt;
-ERROR:  no SQL/JSON item
+ERROR:  no SQL/JSON item found for specified path of column "a"
 SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH '$'   DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt;
  a 
 ---
@@ -637,6 +637,10 @@ SELECT * FROM JSON_TABLE(jsonb '{"a": 123}', '$' || '.' || 'a' COLUMNS (foo int)
 ERROR:  only string constants are supported in JSON_TABLE path specification
 LINE 1: SELECT * FROM JSON_TABLE(jsonb '{"a": 123}', '$' || '.' || '...
                                                      ^
+-- JsonPathQuery() error message mentioning column name
+SELECT * FROM JSON_TABLE('{"a": [{"b": "1"}, {"b": "2"}]}', '$' COLUMNS (b json path '$.a[*].b' ERROR ON ERROR));
+ERROR:  JSON path expression for column "b" should return single item without wrapper
+HINT:  Use WITH WRAPPER clause to wrap SQL/JSON items into array.
 -- JSON_TABLE: nested paths
 -- Duplicate path names
 SELECT * FROM JSON_TABLE(
@@ -849,7 +853,7 @@ SELECT sub.* FROM s,
 		xx int path '$.c',
 		NESTED PATH '$.a.za[1]' columns (NESTED PATH '$.z21[*]' COLUMNS (z21 int path '$?(@ >= $"x")' ERROR ON ERROR))
 	)) sub;
-ERROR:  no SQL/JSON item
+ERROR:  no SQL/JSON item found for specified path of column "z21"
 -- Parent columns xx1, xx appear before NESTED ones
 SELECT sub.* FROM s,
 	(VALUES (23)) x(x), generate_series(13, 13) y,
diff --git a/src/test/regress/expected/sqljson_queryfuncs.out b/src/test/regress/expected/sqljson_queryfuncs.out
index 9e86b0da10..49b014b1ec 100644
--- a/src/test/regress/expected/sqljson_queryfuncs.out
+++ b/src/test/regress/expected/sqljson_queryfuncs.out
@@ -339,7 +339,7 @@ SELECT JSON_VALUE(jsonb '[]', '$');
 (1 row)
 
 SELECT JSON_VALUE(jsonb '[]', '$' ERROR ON ERROR);
-ERROR:  JSON path expression in JSON_VALUE should return singleton scalar item
+ERROR:  JSON path expression in JSON_VALUE should return single scalar item
 SELECT JSON_VALUE(jsonb '{}', '$');
  json_value 
 ------------
@@ -347,7 +347,7 @@ SELECT JSON_VALUE(jsonb '{}', '$');
 (1 row)
 
 SELECT JSON_VALUE(jsonb '{}', '$' ERROR ON ERROR);
-ERROR:  JSON path expression in JSON_VALUE should return singleton scalar item
+ERROR:  JSON path expression in JSON_VALUE should return single scalar item
 SELECT JSON_VALUE(jsonb '1', '$.a');
  json_value 
 ------------
@@ -363,9 +363,9 @@ SELECT JSON_VALUE(jsonb '1', 'strict $.a' DEFAULT 'error' ON ERROR);
 (1 row)
 
 SELECT JSON_VALUE(jsonb '1', 'lax $.a' ERROR ON ERROR);
-ERROR:  no SQL/JSON item
+ERROR:  no SQL/JSON item found for specified path
 SELECT JSON_VALUE(jsonb '1', 'lax $.a' ERROR ON EMPTY ERROR ON ERROR);
-ERROR:  no SQL/JSON item
+ERROR:  no SQL/JSON item found for specified path
 SELECT JSON_VALUE(jsonb '1', 'strict $.a' DEFAULT 2 ON ERROR);
  json_value 
 ------------
@@ -397,9 +397,9 @@ SELECT JSON_VALUE(jsonb '1', 'lax $.a' DEFAULT '2' ON EMPTY DEFAULT '3' ON ERROR
 (1 row)
 
 SELECT JSON_VALUE(jsonb '1', 'lax $.a' ERROR ON EMPTY DEFAULT '3' ON ERROR);
-ERROR:  no SQL/JSON item
+ERROR:  no SQL/JSON item found for specified path
 SELECT JSON_VALUE(jsonb '[1,2]', '$[*]' ERROR ON ERROR);
-ERROR:  JSON path expression in JSON_VALUE should return singleton scalar item
+ERROR:  JSON path expression in JSON_VALUE should return single scalar item
 SELECT JSON_VALUE(jsonb '[1,2]', '$[*]' DEFAULT '0' ON ERROR);
  json_value 
 ------------
@@ -758,7 +758,7 @@ SELECT JSON_QUERY(jsonb '[]', '$[*]' EMPTY OBJECT ON EMPTY);
 (1 row)
 
 SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY);
-ERROR:  no SQL/JSON item
+ERROR:  no SQL/JSON item found for specified path
 SELECT JSON_QUERY(jsonb '[]', '$[*]' DEFAULT '"empty"' ON EMPTY);
  json_query 
 ------------
@@ -766,18 +766,18 @@ SELECT JSON_QUERY(jsonb '[]', '$[*]' DEFAULT '"empty"' ON EMPTY);
 (1 row)
 
 SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY NULL ON ERROR);
-ERROR:  no SQL/JSON item
+ERROR:  no SQL/JSON item found for specified path
 SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY EMPTY ARRAY ON ERROR);
-ERROR:  no SQL/JSON item
+ERROR:  no SQL/JSON item found for specified path
 SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY EMPTY OBJECT ON ERROR);
-ERROR:  no SQL/JSON item
+ERROR:  no SQL/JSON item found for specified path
 SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY ERROR ON ERROR);
-ERROR:  no SQL/JSON item
+ERROR:  no SQL/JSON item found for specified path
 SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON ERROR);
-ERROR:  no SQL/JSON item
+ERROR:  no SQL/JSON item found for specified path
 SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' ERROR ON ERROR);
-ERROR:  JSON path expression in JSON_QUERY should return singleton item without wrapper
-HINT:  Use WITH WRAPPER clause to wrap SQL/JSON item sequence into array.
+ERROR:  JSON path expression in JSON_QUERY should return single item without wrapper
+HINT:  Use WITH WRAPPER clause to wrap SQL/JSON items into array.
 SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' DEFAULT '"empty"' ON ERROR);
  json_query 
 ------------
@@ -1033,7 +1033,7 @@ SELECT JSON_QUERY(jsonb '{"a": 1}', '$.b' RETURNING sqljsonb_int_not_null);
 (1 row)
 
 SELECT JSON_QUERY(jsonb '{"a": 1}', '$.b' RETURNING sqljsonb_int_not_null ERROR ON ERROR);
-ERROR:  no SQL/JSON item
+ERROR:  no SQL/JSON item found for specified path
 -- Test timestamptz passing and output
 SELECT JSON_QUERY(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts);
          json_query          
diff --git a/src/test/regress/sql/sqljson_jsontable.sql b/src/test/regress/sql/sqljson_jsontable.sql
index 3752ccc446..29c0c6ba52 100644
--- a/src/test/regress/sql/sqljson_jsontable.sql
+++ b/src/test/regress/sql/sqljson_jsontable.sql
@@ -290,6 +290,9 @@ FROM JSON_TABLE(
 -- Should fail (not supported)
 SELECT * FROM JSON_TABLE(jsonb '{"a": 123}', '$' || '.' || 'a' COLUMNS (foo int));
 
+-- JsonPathQuery() error message mentioning column name
+SELECT * FROM JSON_TABLE('{"a": [{"b": "1"}, {"b": "2"}]}', '$' COLUMNS (b json path '$.a[*].b' ERROR ON ERROR));
+
 -- JSON_TABLE: nested paths
 
 -- Duplicate path names
-- 
2.43.0

From edcef782abd801f5e52de6fed364fe9db92717e3 Mon Sep 17 00:00:00 2001
From: Amit Langote <amitlan@postgresql.org>
Date: Thu, 18 Apr 2024 09:21:33 +0900
Subject: [PATCH v5 3/3] SQL/JSON: Fix issues with DEFAULT .. ON ERROR / EMPTY

SQL/JSON query functions allow specifying an expression to return
when either of ON ERROR or ON EMPTY condition occurs when evaluating
the JSON path expression.  The parser (transformJsonBehavior()) checks
that the specified expression is one of the supported expressions, but
there are two issues with how the check is done that are fixed in this
commit:

* No check for some expressions related to coercion, such as
  CoerceViaIO, that may appear in the transformed user-specified
  expressions that include cast(s)

* An unsupported expression may be masked by a coercion-related
  expression, which must be flagged by checking the latter's
  argument expression recursively

Reported-by: Jian He <jian.universality@gmail.com>
Author: Jian He <jian.universality@gmail.com>
Author: Amit Langote <amitlangote09@gmail.com>
Discussion: https://postgr.es/m/CACJufxEqhqsfrg_p7EMyo5zak3d767iFDL8vz_4%3DZBHpOtrghw@mail.gmail.com
Discussion: https://postgr.es/m/CACJufxGOerH1QJknm1noh-Kz5FqU4p7QfeZSeVT2tN_4SLXYNg@mail.gmail.com
---
 src/backend/parser/parse_expr.c               | 37 +++++++++++-
 .../regress/expected/sqljson_jsontable.out    | 32 +++++++++++
 .../regress/expected/sqljson_queryfuncs.out   | 57 +++++++++++++++++++
 src/test/regress/sql/sqljson_jsontable.sql    | 16 ++++++
 src/test/regress/sql/sqljson_queryfuncs.sql   | 27 +++++++++
 5 files changed, 167 insertions(+), 2 deletions(-)

diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 34ac17868b..02bc37b38c 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -4616,6 +4616,40 @@ coerceJsonExprOutput(ParseState *pstate, JsonExpr *jsexpr)
 		   (jsexpr->use_io_coercion != jsexpr->use_json_coercion));
 }
 
+/*
+ * Recursively checks if the given expression, or its sub-node in some cases,
+ * is valid for using as an ON ERROR / ON EMPTY DEFAULT expression.
+ */
+static bool
+ValidJsonBehaviorDefaultExpr(Node *expr, void *context)
+{
+	if (expr == NULL)
+		return false;
+
+	switch(nodeTag(expr))
+	{
+		/* Acceptable expression nodes */
+		case T_Const:
+		case T_FuncExpr:
+		case T_OpExpr:
+			return true;
+
+		/* Acceptable iff arg of the following nodes is one of the above */
+		case T_CoerceViaIO:
+		case T_CoerceToDomain:
+		case T_ArrayCoerceExpr:
+		case T_ConvertRowtypeExpr:
+		case T_RelabelType:
+		case T_CollateExpr:
+			return expression_tree_walker(expr, ValidJsonBehaviorDefaultExpr,
+										  context);
+		default:
+			break;
+	}
+
+	return false;
+}
+
 /*
  * Transform a JSON BEHAVIOR clause.
  */
@@ -4636,8 +4670,7 @@ transformJsonBehavior(ParseState *pstate, JsonBehavior *behavior,
 		if (btype == JSON_BEHAVIOR_DEFAULT)
 		{
 			expr = transformExprRecurse(pstate, behavior->expr);
-			if (!IsA(expr, Const) && !IsA(expr, FuncExpr) &&
-				!IsA(expr, OpExpr))
+			if (!ValidJsonBehaviorDefaultExpr(expr, NULL))
 				ereport(ERROR,
 						(errcode(ERRCODE_DATATYPE_MISMATCH),
 						 errmsg("can only specify a constant, non-aggregate function, or operator expression for DEFAULT"),
diff --git a/src/test/regress/expected/sqljson_jsontable.out b/src/test/regress/expected/sqljson_jsontable.out
index 9eecd97f45..af1ed09f9f 100644
--- a/src/test/regress/expected/sqljson_jsontable.out
+++ b/src/test/regress/expected/sqljson_jsontable.out
@@ -217,6 +217,38 @@ FROM json_table_test vals
  [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 11 |     |      | "\"str\""    | ["\"str\""]    | "str"        |    |    | 
 (14 rows)
 
+-- Test using casts in DEFAULT .. ON ERROR expression
+SELECT * FROM JSON_TABLE(jsonb '{"d1": "H"}', '$'
+    COLUMNS (js1 jsonb_test_domain path '$.a2' DEFAULT '"foo1"'::jsonb::text ON ERROR));
+  js1   
+--------
+ "foo1"
+(1 row)
+
+SELECT * FROM JSON_TABLE(jsonb '{"d1": "H"}', '$'
+    COLUMNS (js1 jsonb_test_domain path '$.a2' DEFAULT 'foo'::jsonb_test_domain ON ERROR));
+ERROR:  value for domain jsonb_test_domain violates check constraint "jsonb_test_domain_check"
+SELECT * FROM JSON_TABLE(jsonb '{"d1": "H"}', '$'
+    COLUMNS (js1 jsonb_test_domain path '$.a2' DEFAULT 'foo1'::jsonb_test_domain ON ERROR));
+ js1  
+------
+ foo1
+(1 row)
+
+SELECT * FROM JSON_TABLE(jsonb '{"d1": "foo"}', '$'
+    COLUMNS (js1 jsonb_test_domain path '$.d1' DEFAULT 'foo2'::jsonb_test_domain ON ERROR));
+ js1  
+------
+ foo2
+(1 row)
+
+SELECT * FROM JSON_TABLE(jsonb '{"d1": "foo"}', '$'
+    COLUMNS (js1 oid[] path '$.d2' DEFAULT '{1}'::int[]::oid[] ON ERROR));
+ js1 
+-----
+ {1}
+(1 row)
+
 -- JSON_TABLE: Test backward parsing
 CREATE VIEW jsonb_table_view2 AS
 SELECT * FROM
diff --git a/src/test/regress/expected/sqljson_queryfuncs.out b/src/test/regress/expected/sqljson_queryfuncs.out
index 49b014b1ec..003b7fead6 100644
--- a/src/test/regress/expected/sqljson_queryfuncs.out
+++ b/src/test/regress/expected/sqljson_queryfuncs.out
@@ -1222,6 +1222,63 @@ LINE 1: SELECT JSON_QUERY(js, '$'  RETURNING int DEFAULT (SELECT 1) ...
                                                          ^
 DROP TABLE test_jsonb_mutability;
 DROP FUNCTION ret_setint;
+CREATE DOMAIN jsonb_test_domain AS text CHECK (value <> 'foo');
+SELECT JSON_VALUE(jsonb '{"d1": "H"}', '$.a2' RETURNING jsonb_test_domain DEFAULT 'foo'::jsonb_test_domain ON ERROR);
+ERROR:  value for domain jsonb_test_domain violates check constraint "jsonb_test_domain_check"
+SELECT JSON_VALUE(jsonb '{"d1": "H"}', '$.a2' RETURNING jsonb_test_domain DEFAULT 'foo1'::jsonb_test_domain ON ERROR);
+ json_value 
+------------
+ foo1
+(1 row)
+
+SELECT JSON_VALUE(jsonb '{"d1": "H"}', '$.a2' RETURNING jsonb_test_domain DEFAULT '"foo1"'::jsonb::text ON ERROR);
+ json_value 
+------------
+ "foo1"
+(1 row)
+
+SELECT JSON_VALUE(jsonb '{"d1": "foo"}', '$.a2' RETURNING jsonb_test_domain DEFAULT 'foo1'::jsonb_test_domain ON ERROR);
+ json_value 
+------------
+ foo1
+(1 row)
+
+-- Check the cases where a coercion-related expression is masking an
+-- unsupported expressions
+-- CoerceViaIO
+SELECT JSON_QUERY('"a"', '$.a'  RETURNING int DEFAULT (SELECT '"1"')::jsonb ON ERROR);
+ERROR:  can only specify a constant, non-aggregate function, or operator expression for DEFAULT
+LINE 1: ...CT JSON_QUERY('"a"', '$.a'  RETURNING int DEFAULT (SELECT '"...
+                                                             ^
+-- CoerceToDomain
+SELECT JSON_QUERY('"a"', '$.a' RETURNING jsonb_test_domain DEFAULT (select '"1"')::jsonb_test_domain ON ERROR);
+ERROR:  can only specify a constant, non-aggregate function, or operator expression for DEFAULT
+LINE 1: ...('"a"', '$.a' RETURNING jsonb_test_domain DEFAULT (select '"...
+                                                             ^
+-- RelabelType
+SELECT JSON_QUERY('"a"', '$.a'  RETURNING int DEFAULT (SELECT 1)::oid::int ON ERROR);
+ERROR:  can only specify a constant, non-aggregate function, or operator expression for DEFAULT
+LINE 1: ...CT JSON_QUERY('"a"', '$.a'  RETURNING int DEFAULT (SELECT 1)...
+                                                             ^
+-- ArrayCoerceExpr
+SELECT JSON_QUERY('"a"', '$.a'  RETURNING int[] DEFAULT (SELECT '{1}')::oid[]::int[] ON ERROR);
+ERROR:  can only specify a constant, non-aggregate function, or operator expression for DEFAULT
+LINE 1: ... JSON_QUERY('"a"', '$.a'  RETURNING int[] DEFAULT (SELECT '{...
+                                                             ^
+-- CollateExpr
+SELECT JSON_QUERY('"a"', '$.a'  RETURNING int[] DEFAULT (SELECT '{1}')::text COLLATE "C" ON ERROR);
+ERROR:  can only specify a constant, non-aggregate function, or operator expression for DEFAULT
+LINE 1: ... JSON_QUERY('"a"', '$.a'  RETURNING int[] DEFAULT (SELECT '{...
+                                                             ^
+-- ConvertRowtypeExpr
+CREATE TABLE someparent (a int);
+CREATE TABLE somechild () INHERITS (someparent);
+SELECT JSON_QUERY('"a"', '$.a'  RETURNING someparent DEFAULT (SELECT '(1)')::somechild::someparent ON ERROR);
+ERROR:  can only specify a constant, non-aggregate function, or operator expression for DEFAULT
+LINE 1: ..._QUERY('"a"', '$.a'  RETURNING someparent DEFAULT (SELECT '(...
+                                                             ^
+DROP DOMAIN jsonb_test_domain;
+DROP TABLE someparent, somechild;
 -- Extension: non-constant JSON path
 SELECT JSON_EXISTS(jsonb '{"a": 123}', '$' || '.' || 'a');
  json_exists 
diff --git a/src/test/regress/sql/sqljson_jsontable.sql b/src/test/regress/sql/sqljson_jsontable.sql
index 29c0c6ba52..670c0f69d9 100644
--- a/src/test/regress/sql/sqljson_jsontable.sql
+++ b/src/test/regress/sql/sqljson_jsontable.sql
@@ -116,6 +116,22 @@ FROM json_table_test vals
 	) jt
 	ON true;
 
+-- Test using casts in DEFAULT .. ON ERROR expression
+SELECT * FROM JSON_TABLE(jsonb '{"d1": "H"}', '$'
+    COLUMNS (js1 jsonb_test_domain path '$.a2' DEFAULT '"foo1"'::jsonb::text ON ERROR));
+
+SELECT * FROM JSON_TABLE(jsonb '{"d1": "H"}', '$'
+    COLUMNS (js1 jsonb_test_domain path '$.a2' DEFAULT 'foo'::jsonb_test_domain ON ERROR));
+
+SELECT * FROM JSON_TABLE(jsonb '{"d1": "H"}', '$'
+    COLUMNS (js1 jsonb_test_domain path '$.a2' DEFAULT 'foo1'::jsonb_test_domain ON ERROR));
+
+SELECT * FROM JSON_TABLE(jsonb '{"d1": "foo"}', '$'
+    COLUMNS (js1 jsonb_test_domain path '$.d1' DEFAULT 'foo2'::jsonb_test_domain ON ERROR));
+
+SELECT * FROM JSON_TABLE(jsonb '{"d1": "foo"}', '$'
+    COLUMNS (js1 oid[] path '$.d2' DEFAULT '{1}'::int[]::oid[] ON ERROR));
+
 -- JSON_TABLE: Test backward parsing
 
 CREATE VIEW jsonb_table_view2 AS
diff --git a/src/test/regress/sql/sqljson_queryfuncs.sql b/src/test/regress/sql/sqljson_queryfuncs.sql
index ec330d3b73..71e548e6fa 100644
--- a/src/test/regress/sql/sqljson_queryfuncs.sql
+++ b/src/test/regress/sql/sqljson_queryfuncs.sql
@@ -411,6 +411,33 @@ SELECT JSON_QUERY(js, '$'  RETURNING int DEFAULT (SELECT 1) ON ERROR) FROM test_
 DROP TABLE test_jsonb_mutability;
 DROP FUNCTION ret_setint;
 
+CREATE DOMAIN jsonb_test_domain AS text CHECK (value <> 'foo');
+SELECT JSON_VALUE(jsonb '{"d1": "H"}', '$.a2' RETURNING jsonb_test_domain DEFAULT 'foo'::jsonb_test_domain ON ERROR);
+SELECT JSON_VALUE(jsonb '{"d1": "H"}', '$.a2' RETURNING jsonb_test_domain DEFAULT 'foo1'::jsonb_test_domain ON ERROR);
+SELECT JSON_VALUE(jsonb '{"d1": "H"}', '$.a2' RETURNING jsonb_test_domain DEFAULT '"foo1"'::jsonb::text ON ERROR);
+SELECT JSON_VALUE(jsonb '{"d1": "foo"}', '$.a2' RETURNING jsonb_test_domain DEFAULT 'foo1'::jsonb_test_domain ON ERROR);
+
+-- Check the cases where a coercion-related expression is masking an
+-- unsupported expressions
+
+-- CoerceViaIO
+SELECT JSON_QUERY('"a"', '$.a'  RETURNING int DEFAULT (SELECT '"1"')::jsonb ON ERROR);
+-- CoerceToDomain
+SELECT JSON_QUERY('"a"', '$.a' RETURNING jsonb_test_domain DEFAULT (select '"1"')::jsonb_test_domain ON ERROR);
+-- RelabelType
+SELECT JSON_QUERY('"a"', '$.a'  RETURNING int DEFAULT (SELECT 1)::oid::int ON ERROR);
+-- ArrayCoerceExpr
+SELECT JSON_QUERY('"a"', '$.a'  RETURNING int[] DEFAULT (SELECT '{1}')::oid[]::int[] ON ERROR);
+-- CollateExpr
+SELECT JSON_QUERY('"a"', '$.a'  RETURNING int[] DEFAULT (SELECT '{1}')::text COLLATE "C" ON ERROR);
+-- ConvertRowtypeExpr
+CREATE TABLE someparent (a int);
+CREATE TABLE somechild () INHERITS (someparent);
+SELECT JSON_QUERY('"a"', '$.a'  RETURNING someparent DEFAULT (SELECT '(1)')::somechild::someparent ON ERROR);
+
+DROP DOMAIN jsonb_test_domain;
+DROP TABLE someparent, somechild;
+
 -- Extension: non-constant JSON path
 SELECT JSON_EXISTS(jsonb '{"a": 123}', '$' || '.' || 'a');
 SELECT JSON_VALUE(jsonb '{"a": 123}', '$' || '.' || 'a');
-- 
2.43.0