Add SQL/JSON ON MISMATCH clause to JSON_VALUE

Started by Florents Tselaiabout 14 hours ago2 messages

florents.tselai@gmail.com

about 14 hours ago

1 attachment(s)

Hello hackers,

Here's a patch that attempts to $subject.

My original motivation was to implement this for JSON_TABLE,
but I realized it would be better to start with a smaller scope
by targeting the basic query functions first.

Currently this v1 passes the tests I've put for JSON_VALUE.

I've had trouble making JSON_QUERY work too,
so I suspect that although my tests pass,
there may be something I'm missing in the executor side of things.
While I troubleshoot that it'd be nice to get some feedback for this
version.

Regards,
Flo

Attachments:

v1-0001-Add-SQL-JSON-ON-MISMATCH-clause-to-JSON_VALUE.patchapplication/octet-stream; name=v1-0001-Add-SQL-JSON-ON-MISMATCH-clause-to-JSON_VALUE.patchDownload

From f1a29f2cbf1cc73e122c6c4c2a74262927acd4ed Mon Sep 17 00:00:00 2001
From: Florents Tselai <florents.tselai@gmail.com>
Date: Sat, 24 Jan 2026 19:02:18 +0200
Subject: [PATCH v1] Add SQL/JSON ON MISMATCH clause to JSON_VALUE

This commit implements the standard SQL/JSON ON MISMATCH clause for the JSON_VALU() function.
This feature allows users to define specific behavior when a JSON scalar value cannot be successfully coerced to the target SQL data type.

Previously, coercion failures (such as attempting to cast "not_a_number" to integer, or numeric overflows)
would strictly trigger the ON ERROR clause or raise a runtime exception.
This made it difficult to distinguish between malformed data values
and actual structural issues (such as finding an array where a scalar was expected).
---
 src/backend/executor/execExpr.c               |  62 ++++++++-
 src/backend/executor/execExprInterp.c         |  73 ++++++++++-
 src/backend/parser/gram.y                     |  45 ++++---
 src/backend/parser/parse_expr.c               |  22 +++-
 src/backend/parser/parse_jsontable.c          |   1 +
 src/include/nodes/execnodes.h                 |   3 +
 src/include/nodes/parsenodes.h                |   2 +
 src/include/nodes/primnodes.h                 |   3 +-
 src/include/parser/kwlist.h                   |   1 +
 .../regress/expected/sqljson_queryfuncs.out   | 119 ++++++++++++++++++
 src/test/regress/sql/sqljson_queryfuncs.sql   |  60 +++++++++
 11 files changed, 371 insertions(+), 20 deletions(-)

diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c
index 088eca24021..1e1ba04dfaf 100644
--- a/src/backend/executor/execExpr.c
+++ b/src/backend/executor/execExpr.c
@@ -4831,7 +4831,7 @@ ExecInitJsonExpr(JsonExpr *jsexpr, ExprState *state,
 	scratch->d.constval.isnull = true;
 	ExprEvalPushStep(state, scratch);
 
-	escontext = jsexpr->on_error->btype != JSON_BEHAVIOR_ERROR ?
+	escontext = (jsexpr->on_error->btype != JSON_BEHAVIOR_ERROR || jsexpr->on_mismatch != NULL) ?
 		&jsestate->escontext : NULL;
 
 	/*
@@ -4899,7 +4899,7 @@ ExecInitJsonExpr(JsonExpr *jsexpr, ExprState *state,
 		ExprEvalPushStep(state, scratch);
 	}
 
-	jsestate->jump_empty = jsestate->jump_error = -1;
+	jsestate->jump_empty = jsestate->jump_error = jsestate->jump_mismatch = -1;
 
 	/*
 	 * Step to check jsestate->error and return the ON ERROR expression if
@@ -4968,6 +4968,64 @@ ExecInitJsonExpr(JsonExpr *jsexpr, ExprState *state,
 		ExprEvalPushStep(state, scratch);
 	}
 
+	/*
+	 * Step to check jsestate->mismatch and return the ON MISMATCH expression
+	 * if there is one.
+	 */
+	if (jsexpr->on_mismatch != NULL &&
+		jsexpr->on_mismatch->btype != JSON_BEHAVIOR_ERROR &&
+		(!(IsA(jsexpr->on_mismatch->expr, Const) &&
+		   ((Const *) jsexpr->on_mismatch->expr)->constisnull) ||
+		 returning_domain))
+	{
+		ErrorSaveContext *saved_escontext;
+
+		jsestate->jump_mismatch = state->steps_len;
+
+		/* JUMP to end if mismatch flag is false (skip this handler) */
+		jumps_to_end = lappend_int(jumps_to_end, state->steps_len);
+		scratch->opcode = EEOP_JUMP_IF_NOT_TRUE;
+		scratch->resvalue = &jsestate->mismatch.value;
+		scratch->resnull = &jsestate->mismatch.isnull;
+		scratch->d.jump.jumpdone = -1;	/* set below */
+		ExprEvalPushStep(state, scratch);
+
+		/*
+		 * Evaluate the ON MISMATCH expression (e.g. DEFAULT -1). Use soft
+		 * error handling so we can re-throw safely if needed.
+		 */
+		saved_escontext = state->escontext;
+		state->escontext = escontext;
+		ExecInitExprRec((Expr *) jsexpr->on_mismatch->expr,
+						state, resv, resnull);
+		state->escontext = saved_escontext;
+
+		/* Coerce the result if the DEFAULT value needs casting */
+		if (jsexpr->on_mismatch->coerce)
+			ExecInitJsonCoercion(state, jsexpr->returning, escontext,
+								 jsexpr->omit_quotes, false,
+								 resv, resnull);
+
+		/* Add COERCION_FINISH step to verify the DEFAULT value's validity */
+		if (jsexpr->on_mismatch->coerce ||
+			IsA(jsexpr->on_mismatch->expr, CoerceViaIO) ||
+			IsA(jsexpr->on_mismatch->expr, CoerceToDomain))
+		{
+			scratch->opcode = EEOP_JSONEXPR_COERCION_FINISH;
+			scratch->resvalue = resv;
+			scratch->resnull = resnull;
+			scratch->d.jsonexpr.jsestate = jsestate;
+			ExprEvalPushStep(state, scratch);
+		}
+
+		/* JUMP to end (skip subsequent ON EMPTY checks if we matched here) */
+		jumps_to_end = lappend_int(jumps_to_end, state->steps_len);
+		scratch->opcode = EEOP_JUMP;
+		scratch->d.jump.jumpdone = -1;
+		ExprEvalPushStep(state, scratch);
+	}
+
+
 	/*
 	 * Step to check jsestate->empty and return the ON EMPTY expression if
 	 * there is one.
diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index a7a5ac1e83b..034b314fb95 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -4848,9 +4848,10 @@ ExecEvalJsonExprPath(ExprState *state, ExprEvalStep *op,
 	item = jsestate->formatted_expr.value;
 	path = DatumGetJsonPathP(jsestate->pathspec.value);
 
-	/* Set error/empty to false. */
+	/* Set error/empty/mismatch to false. */
 	memset(&jsestate->error, 0, sizeof(NullableDatum));
 	memset(&jsestate->empty, 0, sizeof(NullableDatum));
+	memset(&jsestate->mismatch, 0, sizeof(NullableDatum));
 
 	/* Also reset ErrorSaveContext contents for the next row. */
 	if (jsestate->escontext.details_wanted)
@@ -4950,6 +4951,10 @@ ExecEvalJsonExprPath(ExprState *state, ExprEvalStep *op,
 		fcinfo->args[0].value = PointerGetDatum(val_string);
 		fcinfo->args[0].isnull = *op->resnull;
 
+		/* Request Error Details so we can see the error code */
+		if (jsexpr->on_mismatch)
+			jsestate->escontext.details_wanted = true;
+
 		/*
 		 * Second and third arguments are already set up in
 		 * ExecInitJsonExpr().
@@ -4958,7 +4963,40 @@ ExecEvalJsonExprPath(ExprState *state, ExprEvalStep *op,
 		fcinfo->isnull = false;
 		*op->resvalue = FunctionCallInvoke(fcinfo);
 		if (SOFT_ERROR_OCCURRED(&jsestate->escontext))
-			error = true;
+		{
+			/* Check for Type Mismatch codes */
+			/*
+			 * * We capture: 1. INVALID_TEXT_REPRESENTATION (e.g. "abc" ->
+			 * int) 2. NUMERIC_VALUE_OUT_OF_RANGE (e.g. 10000000000 -> int4)
+			 * 3. INVALID_DATETIME_FORMAT (if casting to date/timestamp)
+			 */
+			if (jsexpr->on_mismatch &&
+				jsestate->escontext.error_data &&
+				(jsestate->escontext.error_data->sqlerrcode == ERRCODE_INVALID_TEXT_REPRESENTATION ||
+				 jsestate->escontext.error_data->sqlerrcode == ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE ||
+				 jsestate->escontext.error_data->sqlerrcode == ERRCODE_INVALID_DATETIME_FORMAT))
+			{
+				/*
+				 * We must suppress the generic error so ON ERROR
+				 * does not catch it. We will handle the "ON MISMATCH ERROR"
+				 * case manually later.
+				 */
+				jsestate->escontext.error_occurred = false;
+
+				pfree(jsestate->escontext.error_data);
+				jsestate->escontext.error_data = NULL;
+
+				jsestate->mismatch.value = BoolGetDatum(true);
+				jsestate->mismatch.isnull = false;
+
+				*op->resvalue = (Datum) 0;
+				*op->resnull = true;
+			}
+			else
+			{
+				error = true;
+			}
+		}
 	}
 
 	/*
@@ -5025,6 +5063,37 @@ ExecEvalJsonExprPath(ExprState *state, ExprEvalStep *op,
 		return jsestate->jump_error >= 0 ? jsestate->jump_error : jsestate->jump_end;
 	}
 
+	if (DatumGetBool(jsestate->mismatch.value))
+	{
+		/* Logic for MISMATCH found */
+
+		if (jsexpr->on_mismatch->btype == JSON_BEHAVIOR_ERROR)
+		{
+			/*
+			 * If the user asked for ERROR ON MISMATCH, we explicitly
+			 * throw here. We cannot let this fall through, or it will return
+			 * NULL.
+			 */
+			ereport(ERROR,
+					(errcode(ERRCODE_SQL_JSON_ITEM_CANNOT_BE_CAST_TO_TARGET_TYPE),
+					 errmsg("JSON item could not be cast to the target type"),
+					 errhint("Use ON MISMATCH to handle this specific coercion failure.")));
+		}
+		else
+		{
+			/* Handle NULL, DEFAULT, etc. */
+
+			*op->resvalue = (Datum) 0;
+			*op->resnull = true;
+
+			/* Reset context for the DEFAULT expression evaluation */
+			jsestate->escontext.error_occurred = false;
+			jsestate->escontext.details_wanted = true;
+
+			return jsestate->jump_mismatch >= 0 ? jsestate->jump_mismatch : jsestate->jump_end;
+		}
+	}
+
 	return jump_eval_coercion >= 0 ? jump_eval_coercion : jsestate->jump_end;
 }
 
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 713ee5c10a2..7d7eafec6f9 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -754,7 +754,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 	LOCALTIME LOCALTIMESTAMP LOCATION LOCK_P LOCKED LOGGED LSN_P
 
 	MAPPING MATCH MATCHED MATERIALIZED MAXVALUE MERGE MERGE_ACTION METHOD
-	MINUTE_P MINVALUE MODE MONTH_P MOVE
+	MINUTE_P MINVALUE MISMATCH MODE MONTH_P MOVE
 
 	NAME_P NAMES NATIONAL NATURAL NCHAR NESTED NEW NEXT NFC NFD NFKC NFKD NO
 	NONE NORMALIZE NORMALIZED
@@ -14549,7 +14549,8 @@ json_table_column_definition:
 					n->wrapper = $4;
 					n->quotes = $5;
 					n->on_empty = (JsonBehavior *) linitial($6);
-					n->on_error = (JsonBehavior *) lsecond($6);
+					n->on_mismatch = (JsonBehavior *) lsecond($6);
+					n->on_error = (JsonBehavior *) lthird($6);
 					n->location = @1;
 					$$ = (Node *) n;
 				}
@@ -14569,7 +14570,8 @@ json_table_column_definition:
 					n->wrapper = $5;
 					n->quotes = $6;
 					n->on_empty = (JsonBehavior *) linitial($7);
-					n->on_error = (JsonBehavior *) lsecond($7);
+					n->on_mismatch = (JsonBehavior *) lsecond($7);
+					n->on_error = (JsonBehavior *) lthird($7);
 					n->location = @1;
 					$$ = (Node *) n;
 				}
@@ -16415,7 +16417,8 @@ func_expr_common_subexpr:
 					n->wrapper = $8;
 					n->quotes = $9;
 					n->on_empty = (JsonBehavior *) linitial($10);
-					n->on_error = (JsonBehavior *) lsecond($10);
+					n->on_mismatch = (JsonBehavior *) lsecond($10);
+					n->on_error = (JsonBehavior *) lthird($10);
 					n->location = @1;
 					$$ = (Node *) n;
 				}
@@ -16449,7 +16452,8 @@ func_expr_common_subexpr:
 					n->passing = $6;
 					n->output = (JsonOutput *) $7;
 					n->on_empty = (JsonBehavior *) linitial($8);
-					n->on_error = (JsonBehavior *) lsecond($8);
+					n->on_mismatch = (JsonBehavior *) lsecond($8);
+					n->on_error = (JsonBehavior *) lthird($8);
 					n->location = @1;
 					$$ = (Node *) n;
 				}
@@ -17248,15 +17252,26 @@ json_behavior_type:
 		;
 
 json_behavior_clause_opt:
-			json_behavior ON EMPTY_P
-				{ $$ = list_make2($1, NULL); }
-			| json_behavior ON ERROR_P
-				{ $$ = list_make2(NULL, $1); }
-			| json_behavior ON EMPTY_P json_behavior ON ERROR_P
-				{ $$ = list_make2($1, $4); }
-			| /* EMPTY */
-				{ $$ = list_make2(NULL, NULL); }
-		;
+          json_behavior ON EMPTY_P json_behavior ON MISMATCH json_behavior ON ERROR_P
+             { $$ = list_make3($1, $4, $7); }
+
+          | json_behavior ON EMPTY_P json_behavior ON ERROR_P
+             { $$ = list_make3($1, NULL, $4); }
+          | json_behavior ON EMPTY_P json_behavior ON MISMATCH
+             { $$ = list_make3($1, $4, NULL); }
+          | json_behavior ON MISMATCH json_behavior ON ERROR_P
+             { $$ = list_make3(NULL, $1, $4); }
+
+          | json_behavior ON EMPTY_P
+             { $$ = list_make3($1, NULL, NULL); }
+          | json_behavior ON ERROR_P
+             { $$ = list_make3(NULL, NULL, $1); }
+          | json_behavior ON MISMATCH
+             { $$ = list_make3(NULL, $1, NULL); }
+
+          | /* EMPTY */
+             { $$ = list_make3(NULL, NULL, NULL); }
+       ;
 
 json_on_error_clause_opt:
 			json_behavior ON ERROR_P
@@ -18056,6 +18071,7 @@ unreserved_keyword:
 			| METHOD
 			| MINUTE_P
 			| MINVALUE
+			| MISMATCH
 			| MODE
 			| MONTH_P
 			| MOVE
@@ -18676,6 +18692,7 @@ bare_label_keyword:
 			| MERGE_ACTION
 			| METHOD
 			| MINVALUE
+			| MISMATCH
 			| MODE
 			| MOVE
 			| NAME_P
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index dcfe1acc4c3..1d3d58348ee 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -4640,7 +4640,27 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
 													 func->on_empty,
 													 JSON_BEHAVIOR_NULL,
 													 jsexpr->returning);
-			/* Assume NULL ON ERROR when ON ERROR is not specified. */
+			if (func->on_mismatch)
+			{
+				jsexpr->on_mismatch = transformJsonBehavior(pstate,
+															jsexpr,
+															func->on_mismatch,
+															JSON_BEHAVIOR_NULL,
+															jsexpr->returning);
+
+				if (func->on_mismatch != NULL &&
+					func->on_mismatch->btype != JSON_BEHAVIOR_ERROR &&
+					func->on_mismatch->btype != JSON_BEHAVIOR_NULL &&
+					func->on_mismatch->btype != JSON_BEHAVIOR_EMPTY_ARRAY &&
+					func->on_mismatch->btype != JSON_BEHAVIOR_EMPTY_OBJECT &&
+					func->on_mismatch->btype != JSON_BEHAVIOR_DEFAULT)
+				{
+					ereport(ERROR,
+							(errcode(ERRCODE_SYNTAX_ERROR),
+							 errmsg("invalid %s behavior for %s()", "ON MISMATCH", "JSON_VALUE"),
+							 errdetail("Only ERROR, NULL, EMPTY ARRAY, EMPTY OBJECT, or DEFAULT is allowed.")));
+				}
+			}
 			jsexpr->on_error = transformJsonBehavior(pstate,
 													 jsexpr,
 													 func->on_error,
diff --git a/src/backend/parser/parse_jsontable.c b/src/backend/parser/parse_jsontable.c
index c28ae99dee8..54758ba5b01 100644
--- a/src/backend/parser/parse_jsontable.c
+++ b/src/backend/parser/parse_jsontable.c
@@ -436,6 +436,7 @@ transformJsonTableColumn(JsonTableColumn *jtc, Node *contextItemExpr,
 	jfexpr->output->returning = makeNode(JsonReturning);
 	jfexpr->output->returning->format = jtc->format;
 	jfexpr->on_empty = jtc->on_empty;
+	jfexpr->on_mismatch = jtc->on_mismatch;
 	jfexpr->on_error = jtc->on_error;
 	jfexpr->quotes = jtc->quotes;
 	jfexpr->wrapper = jtc->wrapper;
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index f8053d9e572..4ff27970bd9 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -1094,12 +1094,15 @@ typedef struct JsonExprState
 	/* Set to true if the jsonpath evaluation returned 0 items. */
 	NullableDatum empty;
 
+	NullableDatum mismatch;
+
 	/*
 	 * Addresses of steps that implement the non-ERROR variant of ON EMPTY and
 	 * ON ERROR behaviors, respectively.
 	 */
 	int			jump_empty;
 	int			jump_error;
+	int			jump_mismatch;
 
 	/*
 	 * Address of the step to coerce the result value of jsonpath evaluation
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 646d6ced763..cfbc223fdd5 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1886,6 +1886,7 @@ typedef struct JsonFuncExpr
 	List	   *passing;		/* list of PASSING clause arguments, if any */
 	JsonOutput *output;			/* output clause, if specified */
 	JsonBehavior *on_empty;		/* ON EMPTY behavior */
+	JsonBehavior *on_mismatch;	/* ON MISMATCH behavior */
 	JsonBehavior *on_error;		/* ON ERROR behavior */
 	JsonWrapper wrapper;		/* array wrapper behavior (JSON_QUERY only) */
 	JsonQuotes	quotes;			/* omit or keep quotes? (JSON_QUERY only) */
@@ -1953,6 +1954,7 @@ typedef struct JsonTableColumn
 	JsonQuotes	quotes;			/* omit or keep quotes on scalar strings? */
 	List	   *columns;		/* nested columns */
 	JsonBehavior *on_empty;		/* ON EMPTY behavior */
+	JsonBehavior *on_mismatch;	/* ON MISMATCH behavior */
 	JsonBehavior *on_error;		/* ON ERROR behavior */
 	ParseLoc	location;		/* token location, or -1 if unknown */
 } JsonTableColumn;
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 5211cadc258..d4ae3921620 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -1860,8 +1860,9 @@ typedef struct JsonExpr
 	List	   *passing_names;
 	List	   *passing_values;
 
-	/* User-specified or default ON EMPTY and ON ERROR behaviors */
+	/* User-specified or default ON EMPTY, ON_MISMATCH and ON ERROR behaviors */
 	JsonBehavior *on_empty;
+	JsonBehavior *on_mismatch;
 	JsonBehavior *on_error;
 
 	/*
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index f7753c5c8a8..b16520f9c3e 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -281,6 +281,7 @@ PG_KEYWORD("merge_action", MERGE_ACTION, COL_NAME_KEYWORD, BARE_LABEL)
 PG_KEYWORD("method", METHOD, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("minute", MINUTE_P, UNRESERVED_KEYWORD, AS_LABEL)
 PG_KEYWORD("minvalue", MINVALUE, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("mismatch", MISMATCH, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("mode", MODE, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("month", MONTH_P, UNRESERVED_KEYWORD, AS_LABEL)
 PG_KEYWORD("move", MOVE, UNRESERVED_KEYWORD, BARE_LABEL)
diff --git a/src/test/regress/expected/sqljson_queryfuncs.out b/src/test/regress/expected/sqljson_queryfuncs.out
index d1b4b8d99f4..a7835cfb578 100644
--- a/src/test/regress/expected/sqljson_queryfuncs.out
+++ b/src/test/regress/expected/sqljson_queryfuncs.out
@@ -1528,3 +1528,122 @@ SELECT JSON_VALUE(jsonb '1234', '$' RETURNING bit(3)  DEFAULT 1::bit(3) ON ERROR
 SELECT JSON_VALUE(jsonb '"111"', '$.a'  RETURNING bit(3) DEFAULT '1111' ON EMPTY);
 ERROR:  bit string length 4 does not match type bit(3)
 DROP DOMAIN queryfuncs_d_varbit3;
+--
+-- JSON_VALUE: ON MISMATCH
+--
+-- Setup test data
+SELECT '{"str": "not_a_number", "num": 123, "overflow": 9999999999, "arr": [1,2], "obj": {"k": "v"}, "date": "bad_date"}'::jsonb AS js \gset
+SELECT JSON_VALUE(:'js', '$.str' RETURNING int ERROR ON MISMATCH); -- Expected: ERROR: JSON item could not be cast to the target type
+ERROR:  JSON item could not be cast to the target type
+HINT:  Use ON MISMATCH to handle this specific coercion failure.
+SELECT JSON_VALUE(:'js', '$.str' RETURNING int DEFAULT -100 ON MISMATCH); -- Expected: -100
+ json_value 
+------------
+       -100
+(1 row)
+
+SELECT JSON_VALUE(:'js', '$.str' RETURNING int NULL ON MISMATCH); -- Expected: NULL (empty row)
+ json_value 
+------------
+           
+(1 row)
+
+SELECT JSON_VALUE(:'js', '$.str' RETURNING date DEFAULT '1970-01-01'::date ON MISMATCH); -- Expected: 01-01-1970
+ json_value 
+------------
+ 01-01-1970
+(1 row)
+
+SELECT JSON_VALUE(:'js', '$.overflow' RETURNING int DEFAULT -200 ON MISMATCH); -- Expected: -200
+ json_value 
+------------
+       -200
+(1 row)
+
+SELECT JSON_VALUE(:'js', '$.overflow' RETURNING int NULL ON MISMATCH); -- Expected: NULL (empty row)
+ json_value 
+------------
+           
+(1 row)
+
+-- Array -> Scalar (Should hit ON ERROR default, which is NULL)
+SELECT JSON_VALUE(:'js', '$.arr' RETURNING int DEFAULT -300 ON MISMATCH); -- Expected: NULL (empty row)
+ json_value 
+------------
+           
+(1 row)
+
+-- 2. Object -> Scalar (Should hit ON ERROR default, which is NULL)
+SELECT JSON_VALUE(:'js', '$.obj' RETURNING int DEFAULT -300 ON MISMATCH); -- Expected: NULL (empty row)
+ json_value 
+------------
+           
+(1 row)
+
+-- Verify it hits explicit ON ERROR
+SELECT JSON_VALUE(:'js', '$.arr' RETURNING int
+    DEFAULT -300 ON MISMATCH
+    DEFAULT -400 ON ERROR); -- Expected: -400
+ json_value 
+------------
+       -400
+(1 row)
+
+-- Should trigger ON EMPTY, ignoring Mismatch/Error
+SELECT JSON_VALUE(:'js', '$.missing' RETURNING int
+    DEFAULT -1 ON EMPTY
+    DEFAULT -2 ON MISMATCH
+    DEFAULT -3 ON ERROR); -- Expected: -1
+ json_value 
+------------
+         -1
+(1 row)
+
+-- Should trigger ON MISMATCH, ignoring Error
+SELECT JSON_VALUE(:'js', '$.str' RETURNING int
+    DEFAULT -1 ON EMPTY
+    DEFAULT -2 ON MISMATCH
+    DEFAULT -3 ON ERROR); -- Expected: -2
+ json_value 
+------------
+         -2
+(1 row)
+
+-- Should trigger ON ERROR
+SELECT JSON_VALUE(:'js', '$.arr' RETURNING int
+    DEFAULT -1 ON EMPTY
+    DEFAULT -2 ON MISMATCH
+    DEFAULT -3 ON ERROR); -- Expected: -3
+ json_value 
+------------
+         -3
+(1 row)
+
+-- If ON MISMATCH is missing, coercion errors fall through to ON ERROR
+SELECT JSON_VALUE(:'js', '$.str' RETURNING int
+    DEFAULT -1 ON EMPTY
+    DEFAULT -500 ON ERROR); -- Expected: -500
+ json_value 
+------------
+       -500
+(1 row)
+
+SELECT JSON_VALUE(:'js', '$.str' RETURNING int ERROR ON MISMATCH ERROR ON ERROR); -- Expected: ERROR: JSON item could not be cast to the target type (with Hint)
+ERROR:  JSON item could not be cast to the target type
+HINT:  Use ON MISMATCH to handle this specific coercion failure.
+SELECT JSON_VALUE(:'js', '$.arr' RETURNING int ERROR ON MISMATCH ERROR ON ERROR); -- Expected: ERROR: JSON path expression in JSON_VALUE must return single scalar item
+ERROR:  JSON path expression in JSON_VALUE must return single scalar item
+-- 1. Valid Integer
+SELECT JSON_VALUE(:'js', '$.num' RETURNING int DEFAULT -100 ON MISMATCH); -- Expected: 123
+ json_value 
+------------
+        123
+(1 row)
+
+-- 2. Valid String-to-Integer Cast
+SELECT JSON_VALUE('{"num": "123"}'::jsonb, '$.num' RETURNING int DEFAULT -100 ON MISMATCH); -- Expected: 123
+ json_value 
+------------
+        123
+(1 row)
+
diff --git a/src/test/regress/sql/sqljson_queryfuncs.sql b/src/test/regress/sql/sqljson_queryfuncs.sql
index a5d5e256d7f..3b984955d5d 100644
--- a/src/test/regress/sql/sqljson_queryfuncs.sql
+++ b/src/test/regress/sql/sqljson_queryfuncs.sql
@@ -502,3 +502,63 @@ SELECT JSON_VALUE(jsonb '1234', '$' RETURNING bit(3)  DEFAULT 1 ON ERROR);
 SELECT JSON_VALUE(jsonb '1234', '$' RETURNING bit(3)  DEFAULT 1::bit(3) ON ERROR);
 SELECT JSON_VALUE(jsonb '"111"', '$.a'  RETURNING bit(3) DEFAULT '1111' ON EMPTY);
 DROP DOMAIN queryfuncs_d_varbit3;
+
+--
+-- JSON_VALUE: ON MISMATCH
+--
+
+-- Setup test data
+SELECT '{"str": "not_a_number", "num": 123, "overflow": 9999999999, "arr": [1,2], "obj": {"k": "v"}, "date": "bad_date"}'::jsonb AS js \gset
+
+SELECT JSON_VALUE(:'js', '$.str' RETURNING int ERROR ON MISMATCH); -- Expected: ERROR: JSON item could not be cast to the target type
+SELECT JSON_VALUE(:'js', '$.str' RETURNING int DEFAULT -100 ON MISMATCH); -- Expected: -100
+SELECT JSON_VALUE(:'js', '$.str' RETURNING int NULL ON MISMATCH); -- Expected: NULL (empty row)
+SELECT JSON_VALUE(:'js', '$.str' RETURNING date DEFAULT '1970-01-01'::date ON MISMATCH); -- Expected: 01-01-1970
+SELECT JSON_VALUE(:'js', '$.overflow' RETURNING int DEFAULT -200 ON MISMATCH); -- Expected: -200
+
+SELECT JSON_VALUE(:'js', '$.overflow' RETURNING int NULL ON MISMATCH); -- Expected: NULL (empty row)
+
+-- Array -> Scalar (Should hit ON ERROR default, which is NULL)
+SELECT JSON_VALUE(:'js', '$.arr' RETURNING int DEFAULT -300 ON MISMATCH); -- Expected: NULL (empty row)
+
+-- 2. Object -> Scalar (Should hit ON ERROR default, which is NULL)
+SELECT JSON_VALUE(:'js', '$.obj' RETURNING int DEFAULT -300 ON MISMATCH); -- Expected: NULL (empty row)
+
+-- Verify it hits explicit ON ERROR
+SELECT JSON_VALUE(:'js', '$.arr' RETURNING int
+    DEFAULT -300 ON MISMATCH
+    DEFAULT -400 ON ERROR); -- Expected: -400
+
+-- Should trigger ON EMPTY, ignoring Mismatch/Error
+SELECT JSON_VALUE(:'js', '$.missing' RETURNING int
+    DEFAULT -1 ON EMPTY
+    DEFAULT -2 ON MISMATCH
+    DEFAULT -3 ON ERROR); -- Expected: -1
+
+-- Should trigger ON MISMATCH, ignoring Error
+SELECT JSON_VALUE(:'js', '$.str' RETURNING int
+    DEFAULT -1 ON EMPTY
+    DEFAULT -2 ON MISMATCH
+    DEFAULT -3 ON ERROR); -- Expected: -2
+
+-- Should trigger ON ERROR
+SELECT JSON_VALUE(:'js', '$.arr' RETURNING int
+    DEFAULT -1 ON EMPTY
+    DEFAULT -2 ON MISMATCH
+    DEFAULT -3 ON ERROR); -- Expected: -3
+
+-- If ON MISMATCH is missing, coercion errors fall through to ON ERROR
+SELECT JSON_VALUE(:'js', '$.str' RETURNING int
+    DEFAULT -1 ON EMPTY
+    DEFAULT -500 ON ERROR); -- Expected: -500
+
+SELECT JSON_VALUE(:'js', '$.str' RETURNING int ERROR ON MISMATCH ERROR ON ERROR); -- Expected: ERROR: JSON item could not be cast to the target type (with Hint)
+
+
+SELECT JSON_VALUE(:'js', '$.arr' RETURNING int ERROR ON MISMATCH ERROR ON ERROR); -- Expected: ERROR: JSON path expression in JSON_VALUE must return single scalar item
+
+-- 1. Valid Integer
+SELECT JSON_VALUE(:'js', '$.num' RETURNING int DEFAULT -100 ON MISMATCH); -- Expected: 123
+
+-- 2. Valid String-to-Integer Cast
+SELECT JSON_VALUE('{"num": "123"}'::jsonb, '$.num' RETURNING int DEFAULT -100 ON MISMATCH); -- Expected: 123
-- 
2.52.0

Florents Tselai

florents.tselai@gmail.com

about 6 hours ago

In reply to: Florents Tselai (#1)

1 attachment(s)

Re: Add SQL/JSON ON MISMATCH clause to JSON_VALUE

I've had trouble making JSON_QUERY work too,
so I suspect that although my tests pass,
there may be something I'm missing in the executor side of things.

"For those curious, I found the issue: in ExecEvalJsonExprPath, I
had misplaced the check for jsexpr->on_mismatch.
I wasn't setting jsestate->escontext.details_wanted = true early enough.
Without this, the soft error context wasn't capturing the specific SQL
error code needed to identify the mismatch.

Attaching a v2 that implements ON MISMATCH for JSON_QUERY and JSON_TABLE
too.

That said, the semantics of ON ERROR / MISMATCH / EMPTY are complex.
I expect someone with access to and detailed knowledge of the standard
might be able to poke holes in certain edge cases, particularly regarding
precedence.

Attachments:

v2-0001-Add-SQL-JSON-ON-MISMATCH-clause-to-JSON_VALUE-JSO.patchapplication/octet-stream; name=v2-0001-Add-SQL-JSON-ON-MISMATCH-clause-to-JSON_VALUE-JSO.patchDownload

From 672b3deaefb7eca9ff505a578a3ac32c4e3152fe Mon Sep 17 00:00:00 2001
From: Florents Tselai <florents.tselai@gmail.com>
Date: Sun, 25 Jan 2026 01:23:21 +0200
Subject: [PATCH v2] Add SQL/JSON ON MISMATCH clause to JSON_VALUE, JSON_QUERY,
 JSON_TABLE functions.

This commit implements the standard SQL/JSON ON MISMATCH clause for the
JSON_VALUE(), JSON_QUERY() and JSON_TABLE() functions.
This feature allows users to define specific behavior when a JSON scalar value cannot be successfully
coerced to the target SQL data type.

Previously, coercion failures (such as attempting to cast "not_a_number"
to integer, or numeric overflows) would strictly trigger the ON ERROR
clause or raise a runtime exception. This made it difficult to
distinguish between malformed data values (type mismatch) and actual
structural issues (such as finding an array where a scalar was expected).

The new behavior precedence is: ON EMPTY > ON MISMATCH > ON ERROR.
---
 doc/src/sgml/func/func-json.sgml              | 108 ++++++---
 src/backend/executor/execExpr.c               | 100 +++++++-
 src/backend/executor/execExprInterp.c         | 112 ++++++++-
 src/backend/nodes/nodeFuncs.c                 |   1 +
 src/backend/parser/gram.y                     |  45 ++--
 src/backend/parser/parse_expr.c               |  52 +++-
 src/backend/parser/parse_jsontable.c          |   1 +
 src/backend/utils/adt/ruleutils.c             |   3 +
 src/include/nodes/execnodes.h                 |   3 +
 src/include/nodes/parsenodes.h                |   2 +
 src/include/nodes/primnodes.h                 |   3 +-
 src/include/parser/kwlist.h                   |   1 +
 .../regress/expected/sqljson_queryfuncs.out   | 228 ++++++++++++++++++
 src/test/regress/sql/sqljson_queryfuncs.sql   | 140 +++++++++++
 14 files changed, 747 insertions(+), 52 deletions(-)

diff --git a/doc/src/sgml/func/func-json.sgml b/doc/src/sgml/func/func-json.sgml
index 1ec73cff464..6ebcd6ddf7a 100644
--- a/doc/src/sgml/func/func-json.sgml
+++ b/doc/src/sgml/func/func-json.sgml
@@ -3268,6 +3268,7 @@ ERROR:  jsonpath array subscript is out of bounds
 <optional> { <literal>WITHOUT</literal> | <literal>WITH</literal> { <literal>CONDITIONAL</literal> | <optional><literal>UNCONDITIONAL</literal></optional> } } <optional> <literal>ARRAY</literal> </optional> <literal>WRAPPER</literal> </optional>
 <optional> { <literal>KEEP</literal> | <literal>OMIT</literal> } <literal>QUOTES</literal> <optional> <literal>ON SCALAR STRING</literal> </optional> </optional>
 <optional> { <literal>ERROR</literal> | <literal>NULL</literal> | <literal>EMPTY</literal> { <optional> <literal>ARRAY</literal> </optional> | <literal>OBJECT</literal> } | <literal>DEFAULT</literal> <replaceable>expression</replaceable> } <literal>ON EMPTY</literal> </optional>
+<optional> { <literal>ERROR</literal> | <literal>NULL</literal> | <literal>EMPTY</literal> { <optional> <literal>ARRAY</literal> </optional> | <literal>OBJECT</literal> } | <literal>DEFAULT</literal> <replaceable>expression</replaceable> } <literal>ON MISMATCH</literal> </optional>
 <optional> { <literal>ERROR</literal> | <literal>NULL</literal> | <literal>EMPTY</literal> { <optional> <literal>ARRAY</literal> </optional> | <literal>OBJECT</literal> } | <literal>DEFAULT</literal> <replaceable>expression</replaceable> } <literal>ON ERROR</literal> </optional>) <returnvalue>jsonb</returnvalue>
 </synopsis>
       </para>
@@ -3315,17 +3316,21 @@ ERROR:  jsonpath array subscript is out of bounds
        <para>
         The <literal>ON EMPTY</literal> clause specifies the behavior if
         evaluating <replaceable>path_expression</replaceable> yields an empty
-        set. The <literal>ON ERROR</literal> clause specifies the behavior
-        if an error occurs when evaluating <replaceable>path_expression</replaceable>,
-        when coercing the result value to the <literal>RETURNING</literal> type,
-        or when evaluating the <literal>ON EMPTY</literal> expression if the
-        <replaceable>path_expression</replaceable> evaluation returns an empty
         set.
+        The <literal>ON MISMATCH</literal> clause specifies the behavior if
+        the result value cannot be coerced to the <literal>RETURNING</literal> type
+        (for example, if an array is found when a scalar is required, or a string cannot be cast to the target type).
+        The <literal>ON ERROR</literal> clause specifies the behavior
+        if an error occurs when evaluating <replaceable>path_expression</replaceable>.
+        If <literal>ON MISMATCH</literal> is not specified, coercion errors are handled
+        by the <literal>ON ERROR</literal> clause.
+        <literal>ON ERROR</literal> also handles errors occurring during evaluation of
+        <literal>ON EMPTY</literal> or <literal>ON MISMATCH</literal> expressions.
        </para>
       </listitem>
       <listitem>
        <para>
-        For both <literal>ON EMPTY</literal> and <literal>ON ERROR</literal>,
+        For <literal>ON EMPTY</literal>, <literal>ON MISMATCH</literal>, and <literal>ON ERROR</literal>,
         specifying <literal>ERROR</literal> will cause an error to be thrown with
         the appropriate message. Other options include returning an SQL NULL, an
         empty array (<literal>EMPTY <optional>ARRAY</optional></literal>),
@@ -3334,6 +3339,8 @@ ERROR:  jsonpath array subscript is out of bounds
         that can be coerced to jsonb or the type specified in <literal>RETURNING</literal>.
         The default when <literal>ON EMPTY</literal> or <literal>ON ERROR</literal>
         is not specified is to return an SQL NULL value.
+        If <literal>ON MISMATCH</literal> is not specified, it defaults to handling coercion
+        errors via the <literal>ON ERROR</literal> clause.
        </para>
       </listitem>
      </itemizedlist>
@@ -3356,6 +3363,10 @@ ERROR:  malformed array literal: "[1, 2]"
 DETAIL:  Missing "]" after array dimensions.
 </programlisting>
        </para>
+       <para>
+        <literal>JSON_QUERY(jsonb '{"ids": [1, 2]}', '$.ids' RETURNING int DEFAULT 0 ON MISMATCH)</literal>
+        <returnvalue>0</returnvalue>
+       </para>
       </entry>
      </row>
      <row>
@@ -3367,6 +3378,7 @@ DETAIL:  Missing "]" after array dimensions.
 <optional> <literal>PASSING</literal> { <replaceable>value</replaceable> <literal>AS</literal> <replaceable>varname</replaceable> } <optional>, ...</optional></optional>
 <optional> <literal>RETURNING</literal> <replaceable>data_type</replaceable> </optional>
 <optional> { <literal>ERROR</literal> | <literal>NULL</literal> | <literal>DEFAULT</literal> <replaceable>expression</replaceable> } <literal>ON EMPTY</literal> </optional>
+<optional> { <literal>ERROR</literal> | <literal>NULL</literal> | <literal>DEFAULT</literal> <replaceable>expression</replaceable> } <literal>ON MISMATCH</literal> </optional>
 <optional> { <literal>ERROR</literal> | <literal>NULL</literal> | <literal>DEFAULT</literal> <replaceable>expression</replaceable> } <literal>ON ERROR</literal> </optional>) <returnvalue>text</returnvalue>
 </synopsis>
        </para>
@@ -3397,10 +3409,11 @@ DETAIL:  Missing "]" after array dimensions.
       </listitem>
       <listitem>
        <para>
-        The <literal>ON ERROR</literal> and <literal>ON EMPTY</literal>
+        The <literal>ON EMPTY</literal>, <literal>ON MISMATCH</literal>, and <literal>ON ERROR</literal>
         clauses have similar semantics as mentioned in the description of
-        <function>JSON_QUERY</function>, except the set of values returned in
-        lieu of throwing an error is different.
+        <function>JSON_QUERY</function>, except that the set of values returned in
+        lieu of throwing an error is restricted: <literal>EMPTY ARRAY</literal>
+        and <literal>EMPTY OBJECT</literal> are not allowed.
        </para>
       </listitem>
       <listitem>
@@ -3430,6 +3443,10 @@ DETAIL:  Missing "]" after array dimensions.
         <literal>JSON_VALUE(jsonb '[1,2]', 'strict $[*]' DEFAULT 9 ON ERROR)</literal>
         <returnvalue>9</returnvalue>
        </para>
+       <para>
+        <literal>JSON_VALUE(jsonb '{"id": "N/A", "valid_id": 123}', '$.id' RETURNING int DEFAULT -1 ON MISMATCH );</literal>
+        <returnvalue>-1</returnvalue>
+       </para>
       </entry>
      </row>
     </tbody>
@@ -3517,28 +3534,29 @@ DETAIL:  Missing "]" after array dimensions.
    The syntax is:
   </para>
 
-<synopsis>
-JSON_TABLE (
-    <replaceable>context_item</replaceable>, <replaceable>path_expression</replaceable> <optional> AS <replaceable>json_path_name</replaceable> </optional> <optional> PASSING { <replaceable>value</replaceable> AS <replaceable>varname</replaceable> } <optional>, ...</optional> </optional>
-    COLUMNS ( <replaceable class="parameter">json_table_column</replaceable> <optional>, ...</optional> )
-    <optional> { <literal>ERROR</literal> | <literal>EMPTY</literal> <optional>ARRAY</optional>} <literal>ON ERROR</literal> </optional>
-)
-
-<phrase>
-where <replaceable class="parameter">json_table_column</replaceable> is:
-</phrase>
-  <replaceable>name</replaceable> FOR ORDINALITY
-  | <replaceable>name</replaceable> <replaceable>type</replaceable>
-        <optional> FORMAT JSON <optional>ENCODING <literal>UTF8</literal></optional></optional>
-        <optional> PATH <replaceable>path_expression</replaceable> </optional>
-        <optional> { WITHOUT | WITH { CONDITIONAL | <optional>UNCONDITIONAL</optional> } } <optional> ARRAY </optional> WRAPPER </optional>
-        <optional> { KEEP | OMIT } QUOTES <optional> ON SCALAR STRING </optional> </optional>
-        <optional> { ERROR | NULL | EMPTY { <optional>ARRAY</optional> | OBJECT } | DEFAULT <replaceable>expression</replaceable> } ON EMPTY </optional>
-        <optional> { ERROR | NULL | EMPTY { <optional>ARRAY</optional> | 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>path_expression</replaceable> <optional> AS <replaceable>json_path_name</replaceable> </optional> COLUMNS ( <replaceable>json_table_column</replaceable> <optional>, ...</optional> )
-</synopsis>
+  <synopsis>
+   JSON_TABLE (
+   <replaceable>context_item</replaceable>, <replaceable>path_expression</replaceable> <optional> AS <replaceable>json_path_name</replaceable> </optional> <optional> PASSING { <replaceable>value</replaceable> AS <replaceable>varname</replaceable> } <optional>, ...</optional> </optional>
+   COLUMNS ( <replaceable class="parameter">json_table_column</replaceable> <optional>, ...</optional> )
+   <optional> { <literal>ERROR</literal> | <literal>EMPTY</literal> <optional>ARRAY</optional>} <literal>ON ERROR</literal> </optional>
+   )
+
+   <phrase>
+    where <replaceable class="parameter">json_table_column</replaceable> is:
+   </phrase>
+   <replaceable>name</replaceable> FOR ORDINALITY
+   | <replaceable>name</replaceable> <replaceable>type</replaceable>
+   <optional> FORMAT JSON <optional>ENCODING <literal>UTF8</literal></optional></optional>
+   <optional> PATH <replaceable>path_expression</replaceable> </optional>
+   <optional> { WITHOUT | WITH { CONDITIONAL | <optional>UNCONDITIONAL</optional> } } <optional> ARRAY </optional> WRAPPER </optional>
+   <optional> { KEEP | OMIT } QUOTES <optional> ON SCALAR STRING </optional> </optional>
+   <optional> { ERROR | NULL | EMPTY { <optional>ARRAY</optional> | OBJECT } | DEFAULT <replaceable>expression</replaceable> } ON EMPTY </optional>
+   <optional> { ERROR | NULL | EMPTY { <optional>ARRAY</optional> | OBJECT } | DEFAULT <replaceable>expression</replaceable> } ON MISMATCH </optional>
+   <optional> { ERROR | NULL | EMPTY { <optional>ARRAY</optional> | 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>path_expression</replaceable> <optional> AS <replaceable>json_path_name</replaceable> </optional> COLUMNS ( <replaceable>json_table_column</replaceable> <optional>, ...</optional> )
+  </synopsis>
 
   <para>
    Each syntax element is described below in more detail.
@@ -3939,7 +3957,33 @@ COLUMNS (
        1 |          |       |          |       2 | Wonder  |         2 | Craig Doe
 (5 rows)
 </screen>
-
      </para>
+
+<para>
+ This example demonstrates the distinction between <literal>ON MISMATCH</literal>
+ and <literal>ON ERROR</literal> using the <literal>my_films</literal> table.
+ The first column triggers a mismatch because the <literal>"kind"</literal>
+ field exists (value is <literal>"comedy"</literal>) but cannot be cast to an integer.
+ The second column triggers an error because the path is in <literal>strict</literal>
+ mode and the key <literal>"ratings"</literal> does not exist.
+</para>
+
+<programlisting>
+SELECT
+    json_query(js, '$.favorites[0].kind'
+               RETURNING int
+               DEFAULT -1 ON MISMATCH) AS kind_mismatch,
+    json_query(js, 'strict $.favorites[0].ratings'
+               RETURNING int
+               DEFAULT -2 ON ERROR) AS strict_error
+FROM my_films;
+</programlisting>
+
+<screen>
+ kind_mismatch | strict_error
+---------------+--------------
+            -1 |           -2
+(1 row)
+</screen>
   </sect2>
  </sect1>
diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c
index 088eca24021..5d65c1b6cbe 100644
--- a/src/backend/executor/execExpr.c
+++ b/src/backend/executor/execExpr.c
@@ -4748,6 +4748,7 @@ ExecInitJsonExpr(JsonExpr *jsexpr, ExprState *state,
 	ListCell   *argnamelc;
 	List	   *jumps_return_null = NIL;
 	List	   *jumps_to_end = NIL;
+	List	   *jumps_to_mismatch = NIL;
 	ListCell   *lc;
 	ErrorSaveContext *escontext;
 	bool		returning_domain =
@@ -4831,7 +4832,7 @@ ExecInitJsonExpr(JsonExpr *jsexpr, ExprState *state,
 	scratch->d.constval.isnull = true;
 	ExprEvalPushStep(state, scratch);
 
-	escontext = jsexpr->on_error->btype != JSON_BEHAVIOR_ERROR ?
+	escontext = (jsexpr->on_error->btype != JSON_BEHAVIOR_ERROR || jsexpr->on_mismatch != NULL) ?
 		&jsestate->escontext : NULL;
 
 	/*
@@ -4897,9 +4898,38 @@ ExecInitJsonExpr(JsonExpr *jsexpr, ExprState *state,
 		scratch->opcode = EEOP_JSONEXPR_COERCION_FINISH;
 		scratch->d.jsonexpr.jsestate = jsestate;
 		ExprEvalPushStep(state, scratch);
+
+		/* If a mismatch occurred, jump directly to the mismatch handler */
+		if (jsexpr->on_mismatch)
+		{
+			/*
+			 * Step 1: If Mismatch is FALSE, skip the next instruction (the
+			 * unconditional jump). Target: The instruction AFTER the next one
+			 * (Current + 2).
+			 */
+			scratch->opcode = EEOP_JUMP_IF_NOT_TRUE;
+			scratch->resvalue = &jsestate->mismatch.value;
+			scratch->resnull = &jsestate->mismatch.isnull;
+			scratch->d.jump.jumpdone = state->steps_len + 2;
+			ExprEvalPushStep(state, scratch);
+
+			/*
+			 * Step 2: If we are here, Mismatch is TRUE. Jump unconditionally
+			 * to the handler.
+			 */
+			scratch->opcode = EEOP_JUMP;
+			scratch->d.jump.jumpdone = -1;	/* Patched later in the loop */
+			ExprEvalPushStep(state, scratch);
+
+			/*
+			 * Track the UNCONDITIONAL jump (Step 2) so we can resolve it to
+			 * the handler
+			 */
+			jumps_to_mismatch = lappend_int(jumps_to_mismatch, state->steps_len - 1);
+		}
 	}
 
-	jsestate->jump_empty = jsestate->jump_error = -1;
+	jsestate->jump_empty = jsestate->jump_error = jsestate->jump_mismatch = -1;
 
 	/*
 	 * Step to check jsestate->error and return the ON ERROR expression if
@@ -4968,6 +4998,72 @@ ExecInitJsonExpr(JsonExpr *jsexpr, ExprState *state,
 		ExprEvalPushStep(state, scratch);
 	}
 
+	/*
+	 * Step to check jsestate->mismatch and return the ON MISMATCH expression
+	 * if there is one.
+	 */
+	if (jsexpr->on_mismatch != NULL &&
+		jsexpr->on_mismatch->btype != JSON_BEHAVIOR_ERROR &&
+		(!(IsA(jsexpr->on_mismatch->expr, Const) &&
+		   ((Const *) jsexpr->on_mismatch->expr)->constisnull) ||
+		 returning_domain))
+	{
+		ErrorSaveContext *saved_escontext;
+
+		jsestate->jump_mismatch = state->steps_len;
+
+		/* JUMP to end if mismatch flag is false (skip this handler) */
+		jumps_to_end = lappend_int(jumps_to_end, state->steps_len);
+		scratch->opcode = EEOP_JUMP_IF_NOT_TRUE;
+		scratch->resvalue = &jsestate->mismatch.value;
+		scratch->resnull = &jsestate->mismatch.isnull;
+		scratch->d.jump.jumpdone = -1;	/* set below */
+		ExprEvalPushStep(state, scratch);
+
+		/*
+		 * Evaluate the ON MISMATCH expression (e.g. DEFAULT -1). Use soft
+		 * error handling so we can re-throw safely if needed.
+		 */
+		saved_escontext = state->escontext;
+		state->escontext = escontext;
+		ExecInitExprRec((Expr *) jsexpr->on_mismatch->expr,
+						state, resv, resnull);
+		state->escontext = saved_escontext;
+
+		/* Coerce the result if the DEFAULT value needs casting */
+		if (jsexpr->on_mismatch->coerce)
+			ExecInitJsonCoercion(state, jsexpr->returning, escontext,
+								 jsexpr->omit_quotes, false,
+								 resv, resnull);
+
+		/* Add COERCION_FINISH step to verify the DEFAULT value's validity */
+		if (jsexpr->on_mismatch->coerce ||
+			IsA(jsexpr->on_mismatch->expr, CoerceViaIO) ||
+			IsA(jsexpr->on_mismatch->expr, CoerceToDomain))
+		{
+			scratch->opcode = EEOP_JSONEXPR_COERCION_FINISH;
+			scratch->resvalue = resv;
+			scratch->resnull = resnull;
+			scratch->d.jsonexpr.jsestate = jsestate;
+			ExprEvalPushStep(state, scratch);
+		}
+
+		/* Resolve jumps from CoercionFinish to here */
+		foreach(lc, jumps_to_mismatch)
+		{
+			ExprEvalStep *as = &state->steps[lfirst_int(lc)];
+
+			as->d.jump.jumpdone = jsestate->jump_mismatch;
+		}
+
+		/* JUMP to end (skip subsequent ON EMPTY checks if we matched here) */
+		jumps_to_end = lappend_int(jumps_to_end, state->steps_len);
+		scratch->opcode = EEOP_JUMP;
+		scratch->d.jump.jumpdone = -1;
+		ExprEvalPushStep(state, scratch);
+	}
+
+
 	/*
 	 * Step to check jsestate->empty and return the ON EMPTY expression if
 	 * there is one.
diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index a7a5ac1e83b..ad86f41623f 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -4848,9 +4848,10 @@ ExecEvalJsonExprPath(ExprState *state, ExprEvalStep *op,
 	item = jsestate->formatted_expr.value;
 	path = DatumGetJsonPathP(jsestate->pathspec.value);
 
-	/* Set error/empty to false. */
+	/* Set error/empty/mismatch to false. */
 	memset(&jsestate->error, 0, sizeof(NullableDatum));
 	memset(&jsestate->empty, 0, sizeof(NullableDatum));
+	memset(&jsestate->mismatch, 0, sizeof(NullableDatum));
 
 	/* Also reset ErrorSaveContext contents for the next row. */
 	if (jsestate->escontext.details_wanted)
@@ -4935,6 +4936,10 @@ ExecEvalJsonExprPath(ExprState *state, ExprEvalStep *op,
 			return false;
 	}
 
+	/* Request Error Details so we can see the error code */
+	if (jsexpr->on_mismatch)
+		jsestate->escontext.details_wanted = true;
+
 	/*
 	 * Coerce the result value to the RETURNING type by calling its input
 	 * function.
@@ -4958,7 +4963,40 @@ ExecEvalJsonExprPath(ExprState *state, ExprEvalStep *op,
 		fcinfo->isnull = false;
 		*op->resvalue = FunctionCallInvoke(fcinfo);
 		if (SOFT_ERROR_OCCURRED(&jsestate->escontext))
-			error = true;
+		{
+			/* Check for Type Mismatch codes */
+			/*
+			 * * We capture: 1. INVALID_TEXT_REPRESENTATION (e.g. "abc" ->
+			 * int) 2. NUMERIC_VALUE_OUT_OF_RANGE (e.g. 10000000000 -> int4)
+			 * 3. INVALID_DATETIME_FORMAT (if casting to date/timestamp)
+			 */
+			if (jsexpr->on_mismatch &&
+				jsestate->escontext.error_data &&
+				(jsestate->escontext.error_data->sqlerrcode == ERRCODE_INVALID_TEXT_REPRESENTATION ||
+				 jsestate->escontext.error_data->sqlerrcode == ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE ||
+				 jsestate->escontext.error_data->sqlerrcode == ERRCODE_INVALID_DATETIME_FORMAT))
+			{
+				/*
+				 * We must suppress the generic error so ON ERROR does not
+				 * catch it. We will handle the "ON MISMATCH ERROR" case
+				 * manually later.
+				 */
+				jsestate->escontext.error_occurred = false;
+
+				pfree(jsestate->escontext.error_data);
+				jsestate->escontext.error_data = NULL;
+
+				jsestate->mismatch.value = BoolGetDatum(true);
+				jsestate->mismatch.isnull = false;
+
+				*op->resvalue = (Datum) 0;
+				*op->resnull = true;
+			}
+			else
+			{
+				error = true;
+			}
+		}
 	}
 
 	/*
@@ -5025,6 +5063,32 @@ ExecEvalJsonExprPath(ExprState *state, ExprEvalStep *op,
 		return jsestate->jump_error >= 0 ? jsestate->jump_error : jsestate->jump_end;
 	}
 
+	if (DatumGetBool(jsestate->mismatch.value))
+	{
+		if (jsexpr->on_mismatch->btype == JSON_BEHAVIOR_ERROR)
+		{
+			/*
+			 * If the user asked for ERROR ON MISMATCH, we explicitly throw
+			 * here. We cannot let this fall through, or it will return NULL.
+			 */
+			ereport(ERROR,
+					(errcode(ERRCODE_SQL_JSON_ITEM_CANNOT_BE_CAST_TO_TARGET_TYPE),
+					 errmsg("JSON item could not be cast to the target type"),
+					 errhint("Use ON MISMATCH to handle this specific coercion failure.")));
+		}
+		else
+		{
+			*op->resvalue = (Datum) 0;
+			*op->resnull = true;
+
+			/* Reset context for the DEFAULT expression evaluation */
+			jsestate->escontext.error_occurred = false;
+			jsestate->escontext.details_wanted = true;
+
+			return jsestate->jump_mismatch >= 0 ? jsestate->jump_mismatch : jsestate->jump_end;
+		}
+	}
+
 	return jump_eval_coercion >= 0 ? jump_eval_coercion : jsestate->jump_end;
 }
 
@@ -5195,6 +5259,50 @@ ExecEvalJsonCoercionFinish(ExprState *state, ExprEvalStep *op)
 
 	if (SOFT_ERROR_OCCURRED(&jsestate->escontext))
 	{
+		ErrorData  *errdata = jsestate->escontext.error_data;
+
+		/*
+		 * Check for Mismatch codes (Text, Numeric, Date) Check
+		 * jsexpr->on_mismatch, not jump_mismatch, to handle ERROR behavior
+		 * too
+		 */
+		if (jsestate->jsexpr->on_mismatch &&
+			errdata != NULL &&
+			(jsestate->jsexpr->op == JSON_VALUE_OP || jsestate->jsexpr->op == JSON_QUERY_OP) &&
+			(errdata->sqlerrcode == ERRCODE_INVALID_TEXT_REPRESENTATION ||
+			 errdata->sqlerrcode == ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE ||
+			 errdata->sqlerrcode == ERRCODE_INVALID_DATETIME_FORMAT))
+		{
+			/*
+			 * * CASE 1: ERROR ON MISMATCH If the user wants an error, we MUST
+			 * throw it here. We cannot rely on the interpreter loop because
+			 * ExecInitJsonExpr does not generate handler steps for ERROR
+			 * behavior.
+			 */
+			if (jsestate->jsexpr->on_mismatch->btype == JSON_BEHAVIOR_ERROR)
+			{
+				ereport(ERROR,
+						(errcode(ERRCODE_SQL_JSON_ITEM_CANNOT_BE_CAST_TO_TARGET_TYPE),
+						 errmsg("JSON item could not be cast to the target type"),
+						 errhint("Use ON MISMATCH to handle this specific coercion failure.")));
+			}
+
+			/*
+			 * * CASE 2: DEFAULT ... ON MISMATCH Mark mismatch as active. The
+			 * interpreter loop will see this flag and jump to the DEFAULT
+			 * expression we generated in ExecInitJsonExpr.
+			 */
+			jsestate->mismatch.value = BoolGetDatum(true);
+			jsestate->mismatch.isnull = false;
+
+			/* Suppress the generic error so we don't trigger ON ERROR */
+			jsestate->escontext.error_occurred = false;
+			jsestate->escontext.details_wanted = true;
+
+			/* Return immediately to let the interpreter handle the Jump */
+			return;
+		}
+
 		/*
 		 * jsestate->error or jsestate->empty being set means that the error
 		 * occurred when coercing the JsonBehavior value.  Throw the error in
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index d29664ca5d4..88f4c2c5774 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -3422,6 +3422,7 @@ expression_tree_mutator_impl(Node *node,
 				MUTATE(newnode->passing_values, jexpr->passing_values, List *);
 				/* assume mutator does not care about passing_names */
 				MUTATE(newnode->on_empty, jexpr->on_empty, JsonBehavior *);
+				MUTATE(newnode->on_mismatch, jexpr->on_mismatch, JsonBehavior *);
 				MUTATE(newnode->on_error, jexpr->on_error, JsonBehavior *);
 				return (Node *) newnode;
 			}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 713ee5c10a2..7d7eafec6f9 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -754,7 +754,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 	LOCALTIME LOCALTIMESTAMP LOCATION LOCK_P LOCKED LOGGED LSN_P
 
 	MAPPING MATCH MATCHED MATERIALIZED MAXVALUE MERGE MERGE_ACTION METHOD
-	MINUTE_P MINVALUE MODE MONTH_P MOVE
+	MINUTE_P MINVALUE MISMATCH MODE MONTH_P MOVE
 
 	NAME_P NAMES NATIONAL NATURAL NCHAR NESTED NEW NEXT NFC NFD NFKC NFKD NO
 	NONE NORMALIZE NORMALIZED
@@ -14549,7 +14549,8 @@ json_table_column_definition:
 					n->wrapper = $4;
 					n->quotes = $5;
 					n->on_empty = (JsonBehavior *) linitial($6);
-					n->on_error = (JsonBehavior *) lsecond($6);
+					n->on_mismatch = (JsonBehavior *) lsecond($6);
+					n->on_error = (JsonBehavior *) lthird($6);
 					n->location = @1;
 					$$ = (Node *) n;
 				}
@@ -14569,7 +14570,8 @@ json_table_column_definition:
 					n->wrapper = $5;
 					n->quotes = $6;
 					n->on_empty = (JsonBehavior *) linitial($7);
-					n->on_error = (JsonBehavior *) lsecond($7);
+					n->on_mismatch = (JsonBehavior *) lsecond($7);
+					n->on_error = (JsonBehavior *) lthird($7);
 					n->location = @1;
 					$$ = (Node *) n;
 				}
@@ -16415,7 +16417,8 @@ func_expr_common_subexpr:
 					n->wrapper = $8;
 					n->quotes = $9;
 					n->on_empty = (JsonBehavior *) linitial($10);
-					n->on_error = (JsonBehavior *) lsecond($10);
+					n->on_mismatch = (JsonBehavior *) lsecond($10);
+					n->on_error = (JsonBehavior *) lthird($10);
 					n->location = @1;
 					$$ = (Node *) n;
 				}
@@ -16449,7 +16452,8 @@ func_expr_common_subexpr:
 					n->passing = $6;
 					n->output = (JsonOutput *) $7;
 					n->on_empty = (JsonBehavior *) linitial($8);
-					n->on_error = (JsonBehavior *) lsecond($8);
+					n->on_mismatch = (JsonBehavior *) lsecond($8);
+					n->on_error = (JsonBehavior *) lthird($8);
 					n->location = @1;
 					$$ = (Node *) n;
 				}
@@ -17248,15 +17252,26 @@ json_behavior_type:
 		;
 
 json_behavior_clause_opt:
-			json_behavior ON EMPTY_P
-				{ $$ = list_make2($1, NULL); }
-			| json_behavior ON ERROR_P
-				{ $$ = list_make2(NULL, $1); }
-			| json_behavior ON EMPTY_P json_behavior ON ERROR_P
-				{ $$ = list_make2($1, $4); }
-			| /* EMPTY */
-				{ $$ = list_make2(NULL, NULL); }
-		;
+          json_behavior ON EMPTY_P json_behavior ON MISMATCH json_behavior ON ERROR_P
+             { $$ = list_make3($1, $4, $7); }
+
+          | json_behavior ON EMPTY_P json_behavior ON ERROR_P
+             { $$ = list_make3($1, NULL, $4); }
+          | json_behavior ON EMPTY_P json_behavior ON MISMATCH
+             { $$ = list_make3($1, $4, NULL); }
+          | json_behavior ON MISMATCH json_behavior ON ERROR_P
+             { $$ = list_make3(NULL, $1, $4); }
+
+          | json_behavior ON EMPTY_P
+             { $$ = list_make3($1, NULL, NULL); }
+          | json_behavior ON ERROR_P
+             { $$ = list_make3(NULL, NULL, $1); }
+          | json_behavior ON MISMATCH
+             { $$ = list_make3(NULL, $1, NULL); }
+
+          | /* EMPTY */
+             { $$ = list_make3(NULL, NULL, NULL); }
+       ;
 
 json_on_error_clause_opt:
 			json_behavior ON ERROR_P
@@ -18056,6 +18071,7 @@ unreserved_keyword:
 			| METHOD
 			| MINUTE_P
 			| MINVALUE
+			| MISMATCH
 			| MODE
 			| MONTH_P
 			| MOVE
@@ -18676,6 +18692,7 @@ bare_label_keyword:
 			| MERGE_ACTION
 			| METHOD
 			| MINVALUE
+			| MISMATCH
 			| MODE
 			| MOVE
 			| NAME_P
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index dcfe1acc4c3..6b03854d9d7 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -4594,6 +4594,35 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
 													 func->on_empty,
 													 JSON_BEHAVIOR_NULL,
 													 jsexpr->returning);
+
+			/* --- [DEBUG] INSERTED LOGIC --- */
+			if (func->on_mismatch)
+			{
+				jsexpr->on_mismatch = transformJsonBehavior(pstate,
+															jsexpr,
+															func->on_mismatch,
+															JSON_BEHAVIOR_NULL,
+															jsexpr->returning);
+
+				/* Validate behaviors: JSON_QUERY allows EMPTY ARRAY/OBJECT */
+				if (jsexpr->on_mismatch->btype != JSON_BEHAVIOR_ERROR &&
+					jsexpr->on_mismatch->btype != JSON_BEHAVIOR_NULL &&
+					jsexpr->on_mismatch->btype != JSON_BEHAVIOR_EMPTY_ARRAY &&
+					jsexpr->on_mismatch->btype != JSON_BEHAVIOR_EMPTY_OBJECT &&
+					jsexpr->on_mismatch->btype != JSON_BEHAVIOR_DEFAULT)
+				{
+					ereport(ERROR,
+							(errcode(ERRCODE_SYNTAX_ERROR),
+							 errmsg("invalid %s behavior for %s()", "ON MISMATCH", "JSON_QUERY"),
+							 errdetail("Only ERROR, NULL, EMPTY ARRAY, EMPTY OBJECT, or DEFAULT is allowed.")));
+				}
+			}
+			else
+			{
+				/* Explicit NULL so executor knows to skip mismatch check */
+				jsexpr->on_mismatch = NULL;
+			}
+
 			/* Assume NULL ON ERROR when ON ERROR is not specified. */
 			jsexpr->on_error = transformJsonBehavior(pstate,
 													 jsexpr,
@@ -4640,7 +4669,28 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
 													 func->on_empty,
 													 JSON_BEHAVIOR_NULL,
 													 jsexpr->returning);
-			/* Assume NULL ON ERROR when ON ERROR is not specified. */
+			if (func->on_mismatch)
+			{
+				jsexpr->on_mismatch = transformJsonBehavior(pstate,
+															jsexpr,
+															func->on_mismatch,
+															JSON_BEHAVIOR_NULL,
+															jsexpr->returning);
+				if (jsexpr->on_mismatch->btype != JSON_BEHAVIOR_ERROR &&
+					jsexpr->on_mismatch->btype != JSON_BEHAVIOR_NULL &&
+					jsexpr->on_mismatch->btype != JSON_BEHAVIOR_DEFAULT)
+				{
+					ereport(ERROR,
+							(errcode(ERRCODE_SYNTAX_ERROR),
+							 errmsg("invalid %s behavior for %s()", "ON MISMATCH", "JSON_VALUE"),
+							 errdetail("Only ERROR, NULL, EMPTY ARRAY, EMPTY OBJECT, or DEFAULT is allowed.")));
+				}
+			}
+			else
+			{
+				/* Important: Set to NULL so Executor knows it's missing */
+				jsexpr->on_mismatch = NULL;
+			}
 			jsexpr->on_error = transformJsonBehavior(pstate,
 													 jsexpr,
 													 func->on_error,
diff --git a/src/backend/parser/parse_jsontable.c b/src/backend/parser/parse_jsontable.c
index c28ae99dee8..54758ba5b01 100644
--- a/src/backend/parser/parse_jsontable.c
+++ b/src/backend/parser/parse_jsontable.c
@@ -436,6 +436,7 @@ transformJsonTableColumn(JsonTableColumn *jtc, Node *contextItemExpr,
 	jfexpr->output->returning = makeNode(JsonReturning);
 	jfexpr->output->returning->format = jtc->format;
 	jfexpr->on_empty = jtc->on_empty;
+	jfexpr->on_mismatch = jtc->on_mismatch;
 	jfexpr->on_error = jtc->on_error;
 	jfexpr->quotes = jtc->quotes;
 	jfexpr->wrapper = jtc->wrapper;
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index b5a7ad9066e..098d73ca256 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -9244,6 +9244,9 @@ get_json_expr_options(JsonExpr *jsexpr, deparse_context *context,
 	if (jsexpr->on_empty && jsexpr->on_empty->btype != default_behavior)
 		get_json_behavior(jsexpr->on_empty, context, "EMPTY");
 
+	if (jsexpr->on_mismatch && jsexpr->on_mismatch->btype != default_behavior)
+		get_json_behavior(jsexpr->on_mismatch, context, "MISMATCH");
+
 	if (jsexpr->on_error && jsexpr->on_error->btype != default_behavior)
 		get_json_behavior(jsexpr->on_error, context, "ERROR");
 }
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index f8053d9e572..4ff27970bd9 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -1094,12 +1094,15 @@ typedef struct JsonExprState
 	/* Set to true if the jsonpath evaluation returned 0 items. */
 	NullableDatum empty;
 
+	NullableDatum mismatch;
+
 	/*
 	 * Addresses of steps that implement the non-ERROR variant of ON EMPTY and
 	 * ON ERROR behaviors, respectively.
 	 */
 	int			jump_empty;
 	int			jump_error;
+	int			jump_mismatch;
 
 	/*
 	 * Address of the step to coerce the result value of jsonpath evaluation
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 646d6ced763..cfbc223fdd5 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1886,6 +1886,7 @@ typedef struct JsonFuncExpr
 	List	   *passing;		/* list of PASSING clause arguments, if any */
 	JsonOutput *output;			/* output clause, if specified */
 	JsonBehavior *on_empty;		/* ON EMPTY behavior */
+	JsonBehavior *on_mismatch;	/* ON MISMATCH behavior */
 	JsonBehavior *on_error;		/* ON ERROR behavior */
 	JsonWrapper wrapper;		/* array wrapper behavior (JSON_QUERY only) */
 	JsonQuotes	quotes;			/* omit or keep quotes? (JSON_QUERY only) */
@@ -1953,6 +1954,7 @@ typedef struct JsonTableColumn
 	JsonQuotes	quotes;			/* omit or keep quotes on scalar strings? */
 	List	   *columns;		/* nested columns */
 	JsonBehavior *on_empty;		/* ON EMPTY behavior */
+	JsonBehavior *on_mismatch;	/* ON MISMATCH behavior */
 	JsonBehavior *on_error;		/* ON ERROR behavior */
 	ParseLoc	location;		/* token location, or -1 if unknown */
 } JsonTableColumn;
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 5211cadc258..d4ae3921620 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -1860,8 +1860,9 @@ typedef struct JsonExpr
 	List	   *passing_names;
 	List	   *passing_values;
 
-	/* User-specified or default ON EMPTY and ON ERROR behaviors */
+	/* User-specified or default ON EMPTY, ON_MISMATCH and ON ERROR behaviors */
 	JsonBehavior *on_empty;
+	JsonBehavior *on_mismatch;
 	JsonBehavior *on_error;
 
 	/*
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index f7753c5c8a8..b16520f9c3e 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -281,6 +281,7 @@ PG_KEYWORD("merge_action", MERGE_ACTION, COL_NAME_KEYWORD, BARE_LABEL)
 PG_KEYWORD("method", METHOD, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("minute", MINUTE_P, UNRESERVED_KEYWORD, AS_LABEL)
 PG_KEYWORD("minvalue", MINVALUE, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("mismatch", MISMATCH, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("mode", MODE, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("month", MONTH_P, UNRESERVED_KEYWORD, AS_LABEL)
 PG_KEYWORD("move", MOVE, UNRESERVED_KEYWORD, BARE_LABEL)
diff --git a/src/test/regress/expected/sqljson_queryfuncs.out b/src/test/regress/expected/sqljson_queryfuncs.out
index d1b4b8d99f4..c7be0135d12 100644
--- a/src/test/regress/expected/sqljson_queryfuncs.out
+++ b/src/test/regress/expected/sqljson_queryfuncs.out
@@ -1528,3 +1528,231 @@ SELECT JSON_VALUE(jsonb '1234', '$' RETURNING bit(3)  DEFAULT 1::bit(3) ON ERROR
 SELECT JSON_VALUE(jsonb '"111"', '$.a'  RETURNING bit(3) DEFAULT '1111' ON EMPTY);
 ERROR:  bit string length 4 does not match type bit(3)
 DROP DOMAIN queryfuncs_d_varbit3;
+--
+-- JSON_VALUE: ON MISMATCH
+--
+-- Setup test data
+SELECT '{"str": "not_a_number", "num": 123, "overflow": 9999999999, "arr": [1,2], "obj": {"k": "v"}, "date": "bad_date"}'::jsonb AS js \gset
+SELECT JSON_VALUE(:'js', '$.str' RETURNING int ERROR ON MISMATCH); -- Expected: ERROR: JSON item could not be cast to the target type
+ERROR:  JSON item could not be cast to the target type
+HINT:  Use ON MISMATCH to handle this specific coercion failure.
+SELECT JSON_VALUE(:'js', '$.str' RETURNING int DEFAULT -100 ON MISMATCH); -- Expected: -100
+ json_value 
+------------
+       -100
+(1 row)
+
+SELECT JSON_VALUE(:'js', '$.str' RETURNING int NULL ON MISMATCH); -- Expected: NULL (empty row)
+ json_value 
+------------
+           
+(1 row)
+
+SELECT JSON_VALUE(:'js', '$.str' RETURNING date DEFAULT '1970-01-01'::date ON MISMATCH); -- Expected: 01-01-1970
+ json_value 
+------------
+ 01-01-1970
+(1 row)
+
+SELECT JSON_VALUE(:'js', '$.overflow' RETURNING int DEFAULT -200 ON MISMATCH); -- Expected: -200
+ json_value 
+------------
+       -200
+(1 row)
+
+SELECT JSON_VALUE(:'js', '$.overflow' RETURNING int NULL ON MISMATCH); -- Expected: NULL (empty row)
+ json_value 
+------------
+           
+(1 row)
+
+-- Array -> Scalar (Should hit ON ERROR default, which is NULL)
+SELECT JSON_VALUE(:'js', '$.arr' RETURNING int DEFAULT -300 ON MISMATCH); -- Expected: NULL (empty row)
+ json_value 
+------------
+           
+(1 row)
+
+-- 2. Object -> Scalar (Should hit ON ERROR default, which is NULL)
+SELECT JSON_VALUE(:'js', '$.obj' RETURNING int DEFAULT -300 ON MISMATCH); -- Expected: NULL (empty row)
+ json_value 
+------------
+           
+(1 row)
+
+-- Verify it hits explicit ON ERROR
+SELECT JSON_VALUE(:'js', '$.arr' RETURNING int
+    DEFAULT -300 ON MISMATCH
+    DEFAULT -400 ON ERROR); -- Expected: -400
+ json_value 
+------------
+       -400
+(1 row)
+
+-- Should trigger ON EMPTY, ignoring Mismatch/Error
+SELECT JSON_VALUE(:'js', '$.missing' RETURNING int
+    DEFAULT -1 ON EMPTY
+    DEFAULT -2 ON MISMATCH
+    DEFAULT -3 ON ERROR); -- Expected: -1
+ json_value 
+------------
+         -1
+(1 row)
+
+-- Should trigger ON MISMATCH, ignoring Error
+SELECT JSON_VALUE(:'js', '$.str' RETURNING int
+    DEFAULT -1 ON EMPTY
+    DEFAULT -2 ON MISMATCH
+    DEFAULT -3 ON ERROR); -- Expected: -2
+ json_value 
+------------
+         -2
+(1 row)
+
+-- Should trigger ON ERROR
+SELECT JSON_VALUE(:'js', '$.arr' RETURNING int
+    DEFAULT -1 ON EMPTY
+    DEFAULT -2 ON MISMATCH
+    DEFAULT -3 ON ERROR); -- Expected: -3
+ json_value 
+------------
+         -3
+(1 row)
+
+-- If ON MISMATCH is missing, coercion errors fall through to ON ERROR
+SELECT JSON_VALUE(:'js', '$.str' RETURNING int
+    DEFAULT -1 ON EMPTY
+    DEFAULT -500 ON ERROR); -- Expected: -500
+ json_value 
+------------
+       -500
+(1 row)
+
+SELECT JSON_VALUE(:'js', '$.str' RETURNING int ERROR ON MISMATCH ERROR ON ERROR); -- Expected: ERROR: JSON item could not be cast to the target type (with Hint)
+ERROR:  JSON item could not be cast to the target type
+HINT:  Use ON MISMATCH to handle this specific coercion failure.
+SELECT JSON_VALUE(:'js', '$.arr' RETURNING int ERROR ON MISMATCH ERROR ON ERROR); -- Expected: ERROR: JSON path expression in JSON_VALUE must return single scalar item
+ERROR:  JSON path expression in JSON_VALUE must return single scalar item
+-- 1. Valid Integer
+SELECT JSON_VALUE(:'js', '$.num' RETURNING int DEFAULT -100 ON MISMATCH); -- Expected: 123
+ json_value 
+------------
+        123
+(1 row)
+
+-- 2. Valid String-to-Integer Cast
+SELECT JSON_VALUE('{"num": "123"}'::jsonb, '$.num' RETURNING int DEFAULT -100 ON MISMATCH); -- Expected: 123
+ json_value 
+------------
+        123
+(1 row)
+
+--
+-- JSON_QUERY: ON MISMATCH TEST SUITE
+--
+-- Note: JSON_QUERY is distinct because it *can* return Arrays/Objects.
+-- Mismatches here occur when we force a RETURNING type (like int/date)
+-- that cannot represent the found JSON value.
+--
+-- 1. String -> Int Coercion Failure
+-- "str" is "not_a_number". Cannot cast to int.
+SELECT JSON_QUERY(:'js', '$.str'
+    RETURNING int
+       OMIT QUOTES
+       DEFAULT -1 ON MISMATCH) AS str_to_int; -- Expected: -1
+ str_to_int 
+------------
+         -1
+(1 row)
+
+-- 2. Date Format Failure
+-- "date" is "bad_date". Cannot cast to date.
+SELECT JSON_QUERY(:'js', '$.date'
+    RETURNING date
+       OMIT QUOTES
+       DEFAULT '1900-01-01'::date ON MISMATCH) AS bad_date; -- Expected: 01-01-1900
+  bad_date  
+------------
+ 01-01-1900
+(1 row)
+
+-- 3. Numeric Overflow
+-- "overflow" is 9999999999. Too big for standard integer (int4).
+SELECT JSON_QUERY(:'js', '$.overflow'
+    RETURNING int
+       OMIT QUOTES
+       DEFAULT -999 ON MISMATCH) AS overflow_check; -- Expected: -999
+ overflow_check 
+----------------
+           -999
+(1 row)
+
+-- 4. Structure -> Scalar Mismatch (Unique to JSON_QUERY vs VALUE)
+-- "arr" is [1, 2].
+-- JSON_QUERY finds it successfully (unlike JSON_VALUE which might error on cardinality).
+-- However, we ask for RETURNING int. [1,2] cannot be cast to int.
+SELECT JSON_QUERY(:'js', '$.arr'
+    RETURNING int
+       OMIT QUOTES
+       DEFAULT -10 ON MISMATCH) AS array_to_int; -- Expected: -10
+ array_to_int 
+--------------
+          -10
+(1 row)
+
+-- 5. Object -> Scalar Mismatch
+-- "obj" is {"k": "v"}. Cannot cast object to int.
+SELECT JSON_QUERY(:'js', '$.obj'
+    RETURNING int
+       OMIT QUOTES
+       DEFAULT -20 ON MISMATCH) AS obj_to_int; -- Expected: -20
+ obj_to_int 
+------------
+        -20
+(1 row)
+
+-- 6. Precedence Check: EMPTY vs MISMATCH
+-- "missing" does not exist. Should trigger ON EMPTY, not ON MISMATCH.
+SELECT JSON_QUERY(:'js', '$.missing'
+    RETURNING int
+       OMIT QUOTES
+       DEFAULT -1 ON EMPTY
+       DEFAULT -2 ON MISMATCH
+       DEFAULT -3 ON ERROR) AS precedence_empty; -- Expected: -1
+ precedence_empty 
+------------------
+               -1
+(1 row)
+
+-- 7. Precedence Check: MISMATCH vs ERROR
+-- "str" exists but is "not_a_number". Should trigger ON MISMATCH.
+SELECT JSON_QUERY(:'js', '$.str'
+    RETURNING int
+       OMIT QUOTES
+       DEFAULT -1 ON EMPTY
+       DEFAULT -2 ON MISMATCH
+       DEFAULT -3 ON ERROR) AS precedence_mismatch; -- Expected: -2
+ precedence_mismatch 
+---------------------
+                  -2
+(1 row)
+
+-- 8. "Clean" Pass-through (Control Test)
+-- "num" is 123. Valid int. Should return 123.
+SELECT JSON_QUERY(:'js', '$.num'
+    RETURNING int
+       OMIT QUOTES
+       DEFAULT -1 ON MISMATCH) AS valid_int; -- Expected: 123
+ valid_int 
+-----------
+       123
+(1 row)
+
+-- 9. ERROR ON MISMATCH (Explicit)
+-- Should fail with the specific hint message
+SELECT JSON_QUERY(:'js', '$.str'
+    RETURNING int
+       OMIT QUOTES
+       ERROR ON MISMATCH); -- Expected: ERROR: JSON item could not be cast to the target type -- Hint: Use ON MISMATCH to handle this specific coercion failure.
+ERROR:  JSON item could not be cast to the target type
+HINT:  Use ON MISMATCH to handle this specific coercion failure.
diff --git a/src/test/regress/sql/sqljson_queryfuncs.sql b/src/test/regress/sql/sqljson_queryfuncs.sql
index a5d5e256d7f..470778bf23c 100644
--- a/src/test/regress/sql/sqljson_queryfuncs.sql
+++ b/src/test/regress/sql/sqljson_queryfuncs.sql
@@ -502,3 +502,143 @@ SELECT JSON_VALUE(jsonb '1234', '$' RETURNING bit(3)  DEFAULT 1 ON ERROR);
 SELECT JSON_VALUE(jsonb '1234', '$' RETURNING bit(3)  DEFAULT 1::bit(3) ON ERROR);
 SELECT JSON_VALUE(jsonb '"111"', '$.a'  RETURNING bit(3) DEFAULT '1111' ON EMPTY);
 DROP DOMAIN queryfuncs_d_varbit3;
+
+--
+-- JSON_VALUE: ON MISMATCH
+--
+
+-- Setup test data
+SELECT '{"str": "not_a_number", "num": 123, "overflow": 9999999999, "arr": [1,2], "obj": {"k": "v"}, "date": "bad_date"}'::jsonb AS js \gset
+
+SELECT JSON_VALUE(:'js', '$.str' RETURNING int ERROR ON MISMATCH); -- Expected: ERROR: JSON item could not be cast to the target type
+SELECT JSON_VALUE(:'js', '$.str' RETURNING int DEFAULT -100 ON MISMATCH); -- Expected: -100
+SELECT JSON_VALUE(:'js', '$.str' RETURNING int NULL ON MISMATCH); -- Expected: NULL (empty row)
+SELECT JSON_VALUE(:'js', '$.str' RETURNING date DEFAULT '1970-01-01'::date ON MISMATCH); -- Expected: 01-01-1970
+SELECT JSON_VALUE(:'js', '$.overflow' RETURNING int DEFAULT -200 ON MISMATCH); -- Expected: -200
+
+SELECT JSON_VALUE(:'js', '$.overflow' RETURNING int NULL ON MISMATCH); -- Expected: NULL (empty row)
+
+-- Array -> Scalar (Should hit ON ERROR default, which is NULL)
+SELECT JSON_VALUE(:'js', '$.arr' RETURNING int DEFAULT -300 ON MISMATCH); -- Expected: NULL (empty row)
+
+-- 2. Object -> Scalar (Should hit ON ERROR default, which is NULL)
+SELECT JSON_VALUE(:'js', '$.obj' RETURNING int DEFAULT -300 ON MISMATCH); -- Expected: NULL (empty row)
+
+-- Verify it hits explicit ON ERROR
+SELECT JSON_VALUE(:'js', '$.arr' RETURNING int
+    DEFAULT -300 ON MISMATCH
+    DEFAULT -400 ON ERROR); -- Expected: -400
+
+-- Should trigger ON EMPTY, ignoring Mismatch/Error
+SELECT JSON_VALUE(:'js', '$.missing' RETURNING int
+    DEFAULT -1 ON EMPTY
+    DEFAULT -2 ON MISMATCH
+    DEFAULT -3 ON ERROR); -- Expected: -1
+
+-- Should trigger ON MISMATCH, ignoring Error
+SELECT JSON_VALUE(:'js', '$.str' RETURNING int
+    DEFAULT -1 ON EMPTY
+    DEFAULT -2 ON MISMATCH
+    DEFAULT -3 ON ERROR); -- Expected: -2
+
+-- Should trigger ON ERROR
+SELECT JSON_VALUE(:'js', '$.arr' RETURNING int
+    DEFAULT -1 ON EMPTY
+    DEFAULT -2 ON MISMATCH
+    DEFAULT -3 ON ERROR); -- Expected: -3
+
+-- If ON MISMATCH is missing, coercion errors fall through to ON ERROR
+SELECT JSON_VALUE(:'js', '$.str' RETURNING int
+    DEFAULT -1 ON EMPTY
+    DEFAULT -500 ON ERROR); -- Expected: -500
+
+SELECT JSON_VALUE(:'js', '$.str' RETURNING int ERROR ON MISMATCH ERROR ON ERROR); -- Expected: ERROR: JSON item could not be cast to the target type (with Hint)
+
+
+SELECT JSON_VALUE(:'js', '$.arr' RETURNING int ERROR ON MISMATCH ERROR ON ERROR); -- Expected: ERROR: JSON path expression in JSON_VALUE must return single scalar item
+
+-- 1. Valid Integer
+SELECT JSON_VALUE(:'js', '$.num' RETURNING int DEFAULT -100 ON MISMATCH); -- Expected: 123
+
+-- 2. Valid String-to-Integer Cast
+SELECT JSON_VALUE('{"num": "123"}'::jsonb, '$.num' RETURNING int DEFAULT -100 ON MISMATCH); -- Expected: 123
+
+--
+-- JSON_QUERY: ON MISMATCH TEST SUITE
+--
+-- Note: JSON_QUERY is distinct because it *can* return Arrays/Objects.
+-- Mismatches here occur when we force a RETURNING type (like int/date)
+-- that cannot represent the found JSON value.
+--
+
+-- 1. String -> Int Coercion Failure
+-- "str" is "not_a_number". Cannot cast to int.
+SELECT JSON_QUERY(:'js', '$.str'
+    RETURNING int
+       OMIT QUOTES
+       DEFAULT -1 ON MISMATCH) AS str_to_int; -- Expected: -1
+
+
+-- 2. Date Format Failure
+-- "date" is "bad_date". Cannot cast to date.
+SELECT JSON_QUERY(:'js', '$.date'
+    RETURNING date
+       OMIT QUOTES
+       DEFAULT '1900-01-01'::date ON MISMATCH) AS bad_date; -- Expected: 01-01-1900
+
+
+-- 3. Numeric Overflow
+-- "overflow" is 9999999999. Too big for standard integer (int4).
+SELECT JSON_QUERY(:'js', '$.overflow'
+    RETURNING int
+       OMIT QUOTES
+       DEFAULT -999 ON MISMATCH) AS overflow_check; -- Expected: -999
+
+
+-- 4. Structure -> Scalar Mismatch (Unique to JSON_QUERY vs VALUE)
+-- "arr" is [1, 2].
+-- JSON_QUERY finds it successfully (unlike JSON_VALUE which might error on cardinality).
+-- However, we ask for RETURNING int. [1,2] cannot be cast to int.
+SELECT JSON_QUERY(:'js', '$.arr'
+    RETURNING int
+       OMIT QUOTES
+       DEFAULT -10 ON MISMATCH) AS array_to_int; -- Expected: -10
+
+-- 5. Object -> Scalar Mismatch
+-- "obj" is {"k": "v"}. Cannot cast object to int.
+SELECT JSON_QUERY(:'js', '$.obj'
+    RETURNING int
+       OMIT QUOTES
+       DEFAULT -20 ON MISMATCH) AS obj_to_int; -- Expected: -20
+
+-- 6. Precedence Check: EMPTY vs MISMATCH
+-- "missing" does not exist. Should trigger ON EMPTY, not ON MISMATCH.
+SELECT JSON_QUERY(:'js', '$.missing'
+    RETURNING int
+       OMIT QUOTES
+       DEFAULT -1 ON EMPTY
+       DEFAULT -2 ON MISMATCH
+       DEFAULT -3 ON ERROR) AS precedence_empty; -- Expected: -1
+
+-- 7. Precedence Check: MISMATCH vs ERROR
+-- "str" exists but is "not_a_number". Should trigger ON MISMATCH.
+SELECT JSON_QUERY(:'js', '$.str'
+    RETURNING int
+       OMIT QUOTES
+       DEFAULT -1 ON EMPTY
+       DEFAULT -2 ON MISMATCH
+       DEFAULT -3 ON ERROR) AS precedence_mismatch; -- Expected: -2
+
+-- 8. "Clean" Pass-through (Control Test)
+-- "num" is 123. Valid int. Should return 123.
+SELECT JSON_QUERY(:'js', '$.num'
+    RETURNING int
+       OMIT QUOTES
+       DEFAULT -1 ON MISMATCH) AS valid_int; -- Expected: 123
+
+-- 9. ERROR ON MISMATCH (Explicit)
+-- Should fail with the specific hint message
+SELECT JSON_QUERY(:'js', '$.str'
+    RETURNING int
+       OMIT QUOTES
+       ERROR ON MISMATCH); -- Expected: ERROR: JSON item could not be cast to the target type -- Hint: Use ON MISMATCH to handle this specific coercion failure.
-- 
2.52.0