v1-0001-Add-.translate-from-to-jsonpath-method.patch

From f78d003c271cf72c60673deb6a14823c5c97d015 Mon Sep 17 00:00:00 2001
From: Florents Tselai <florents.tselai@gmail.com>
Date: Sat, 11 Apr 2026 17:53:50 +0300
Subject: [PATCH v1 1/3] Add $.translate(from, to) jsonpath method

---
 doc/src/sgml/func/func-json.sgml              | 18 ++++++++
 src/backend/utils/adt/jsonpath.c              | 19 ++++++++-
 src/backend/utils/adt/jsonpath_exec.c         | 18 ++++++--
 src/backend/utils/adt/jsonpath_gram.y         |  5 ++-
 src/backend/utils/adt/jsonpath_scan.l         |  1 +
 src/include/utils/jsonpath.h                  |  1 +
 src/test/regress/expected/jsonb_jsonpath.out  | 42 +++++++++++++++++++
 src/test/regress/expected/jsonpath.out        |  6 +++
 .../regress/expected/sqljson_queryfuncs.out   |  1 +
 src/test/regress/sql/jsonb_jsonpath.sql       | 11 +++++
 src/test/regress/sql/jsonpath.sql             |  1 +
 src/test/regress/sql/sqljson_queryfuncs.sql   |  1 +
 12 files changed, 119 insertions(+), 5 deletions(-)

diff --git a/doc/src/sgml/func/func-json.sgml b/doc/src/sgml/func/func-json.sgml
index 4cd338fe6e3..ad1210fe965 100644
--- a/doc/src/sgml/func/func-json.sgml
+++ b/doc/src/sgml/func/func-json.sgml
@@ -2840,6 +2840,24 @@ ERROR:  jsonpath member accessor can only be applied to an object
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <replaceable>string</replaceable> <literal>.</literal> <literal>translate(<replaceable>from</replaceable>, <replaceable>to</replaceable>)</literal>
+        <returnvalue><replaceable>string</replaceable></returnvalue>
+       </para>
+        <para>
+         String where each character that matches a character in the
+         <replaceable>from</replaceable> string is replaced with the corresponding
+         character in the <replaceable>to</replaceable> string. If <replaceable>from</replaceable>
+         is longer than <replaceable>to</replaceable>, occurrences of the extra characters in
+         <replaceable>from</replaceable> are deleted.
+        </para>
+        <para>
+         <literal>jsonb_path_query('"12345"', '$.translate("143", "ax")')</literal>
+         <returnvalue>"a2x5"</returnvalue>
+        </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <replaceable>string</replaceable> <literal>.</literal> <literal>split_part(<replaceable>delimiter</replaceable>, <replaceable>n</replaceable>)</literal>
diff --git a/src/backend/utils/adt/jsonpath.c b/src/backend/utils/adt/jsonpath.c
index 7bfc18c9888..6416d3c376e 100644
--- a/src/backend/utils/adt/jsonpath.c
+++ b/src/backend/utils/adt/jsonpath.c
@@ -300,6 +300,7 @@ flattenJsonPathParseItem(StringInfo buf, int *result, struct Node *escontext,
 		case jpiDecimal:
 		case jpiStrReplace:
 		case jpiStrSplitPart:
+		case jpiStrTranslate:
 			{
 				/*
 				 * First, reserve place for left/right arg's positions, then
@@ -893,6 +894,15 @@ printJsonPathItem(StringInfo buf, JsonPathItem *v, bool inKey,
 		case jpiStrInitcap:
 			appendStringInfoString(buf, ".initcap()");
 			break;
+		case jpiStrTranslate:
+			appendStringInfoString(buf, ".translate(");
+			jspGetLeftArg(v, &elem);
+			printJsonPathItem(buf, &elem, false, false);
+			appendStringInfoChar(buf, ',');
+			jspGetRightArg(v, &elem);
+			printJsonPathItem(buf, &elem, false, false);
+			appendStringInfoChar(buf, ')');
+			break;
 		default:
 			elog(ERROR, "unrecognized jsonpath item type: %d", v->type);
 	}
@@ -992,6 +1002,8 @@ jspOperationName(JsonPathItemType type)
 			return "initcap";
 		case jpiStrSplitPart:
 			return "split_part";
+		case jpiStrTranslate:
+			return "translate";
 		default:
 			elog(ERROR, "unrecognized jsonpath item type: %d", type);
 			return NULL;
@@ -1123,6 +1135,7 @@ jspInitByBuffer(JsonPathItem *v, char *base, int32 pos)
 		case jpiStartsWith:
 		case jpiDecimal:
 		case jpiStrReplace:
+		case jpiStrTranslate:
 		case jpiStrSplitPart:
 			read_int32(v->content.args.left, base, pos);
 			read_int32(v->content.args.right, base, pos);
@@ -1249,7 +1262,8 @@ jspGetNext(JsonPathItem *v, JsonPathItem *a)
 			   v->type == jpiStrRtrim ||
 			   v->type == jpiStrBtrim ||
 			   v->type == jpiStrInitcap ||
-			   v->type == jpiStrSplitPart);
+			   v->type == jpiStrSplitPart ||
+			   v->type == jpiStrTranslate);
 
 		if (a)
 			jspInitByBuffer(a, v->base, v->nextPos);
@@ -1278,6 +1292,7 @@ jspGetLeftArg(JsonPathItem *v, JsonPathItem *a)
 		   v->type == jpiStartsWith ||
 		   v->type == jpiDecimal ||
 		   v->type == jpiStrReplace ||
+		   v->type == jpiStrTranslate ||
 		   v->type == jpiStrSplitPart);
 
 	jspInitByBuffer(a, v->base, v->content.args.left);
@@ -1302,6 +1317,7 @@ jspGetRightArg(JsonPathItem *v, JsonPathItem *a)
 		   v->type == jpiStartsWith ||
 		   v->type == jpiDecimal ||
 		   v->type == jpiStrReplace ||
+		   v->type == jpiStrTranslate ||
 		   v->type == jpiStrSplitPart);
 
 	jspInitByBuffer(a, v->base, v->content.args.right);
@@ -1610,6 +1626,7 @@ jspIsMutableWalker(JsonPathItem *jpi, struct JsonPathMutableContext *cxt)
 			case jpiStrBtrim:
 			case jpiStrInitcap:
 			case jpiStrSplitPart:
+			case jpiStrTranslate:
 				status = jpdsNonDateTime;
 				break;
 
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index 770840a0611..687415a04b7 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -1690,6 +1690,7 @@ executeItemOptUnwrapTarget(JsonPathExecContext *cxt, JsonPathItem *jsp,
 		case jpiStrBtrim:
 		case jpiStrInitcap:
 		case jpiStrSplitPart:
+		case jpiStrTranslate:
 			{
 				if (unwrap && JsonbType(jb) == jbvArray)
 					return executeItemUnwrapTargetArray(cxt, jsp, jb, found, false);
@@ -2921,6 +2922,7 @@ executeStringInternalMethod(JsonPathExecContext *cxt, JsonPathItem *jsp,
 		   jsp->type == jpiStrRtrim ||
 		   jsp->type == jpiStrBtrim ||
 		   jsp->type == jpiStrInitcap ||
+		   jsp->type == jpiStrTranslate ||
 		   jsp->type == jpiStrSplitPart);
 
 	if (!(jb = getScalar(jb, jbvString)))
@@ -2935,23 +2937,33 @@ executeStringInternalMethod(JsonPathExecContext *cxt, JsonPathItem *jsp,
 	switch (jsp->type)
 	{
 		case jpiStrReplace:
+		case jpiStrTranslate:
 			{
 				char	   *from_str,
 						   *to_str;
+				PGFunction	func;
 
 				jspGetLeftArg(jsp, &elem);
 				if (elem.type != jpiString)
-					elog(ERROR, "invalid jsonpath item type for .replace() from");
+					elog(ERROR, "invalid jsonpath item type for .%s() from",
+						 jspOperationName(jsp->type));
 
 				from_str = jspGetString(&elem, NULL);
 
 				jspGetRightArg(jsp, &elem);
 				if (elem.type != jpiString)
-					elog(ERROR, "invalid jsonpath item type for .replace() to");
+					elog(ERROR, "invalid jsonpath item type for .%s() to",
+						 jspOperationName(jsp->type));
 
 				to_str = jspGetString(&elem, NULL);
 
-				resStr = TextDatumGetCString(DirectFunctionCall3Coll(replace_text,
+				/* Dispatch to the correct internal function */
+				if (jsp->type == jpiStrReplace)
+					func = replace_text;
+				else
+					func = translate;
+
+				resStr = TextDatumGetCString(DirectFunctionCall3Coll(func,
 																	 DEFAULT_COLLATION_OID,
 																	 str,
 																	 CStringGetTextDatum(from_str),
diff --git a/src/backend/utils/adt/jsonpath_gram.y b/src/backend/utils/adt/jsonpath_gram.y
index f826697d098..2b55350df23 100644
--- a/src/backend/utils/adt/jsonpath_gram.y
+++ b/src/backend/utils/adt/jsonpath_gram.y
@@ -87,7 +87,7 @@ static bool makeItemLikeRegex(JsonPathParseItem *expr,
 %token	<str>		BIGINT_P BOOLEAN_P DATE_P DECIMAL_P INTEGER_P NUMBER_P
 %token	<str>		STRINGFUNC_P TIME_P TIME_TZ_P TIMESTAMP_P TIMESTAMP_TZ_P
 %token	<str>		STR_REPLACE_P STR_LOWER_P STR_UPPER_P STR_LTRIM_P STR_RTRIM_P STR_BTRIM_P
-					STR_INITCAP_P STR_SPLIT_PART_P
+					STR_INITCAP_P STR_SPLIT_PART_P STR_TRANSLATE_P
 
 %type	<result>	result
 
@@ -282,6 +282,8 @@ accessor_op:
 		{ $$ = makeItemUnary(jpiTimestampTz, $4); }
 	| '.' STR_REPLACE_P '(' str_str_args ')'
 		{ $$ = makeItemBinary(jpiStrReplace, linitial($4), lsecond($4)); }
+	| '.' STR_TRANSLATE_P '(' str_str_args ')'
+		{ $$ = makeItemBinary(jpiStrTranslate, linitial($4), lsecond($4)); }
 	| '.' STR_SPLIT_PART_P '(' str_int_args ')'
 		{ $$ = makeItemBinary(jpiStrSplitPart, linitial($4), lsecond($4)); }
 	| '.' STR_LTRIM_P '(' opt_str_arg ')'
@@ -381,6 +383,7 @@ key_name:
 	| STR_UPPER_P
 	| STR_INITCAP_P
 	| STR_REPLACE_P
+	| STR_TRANSLATE_P
 	| STR_SPLIT_PART_P
 	| STR_LTRIM_P
 	| STR_RTRIM_P
diff --git a/src/backend/utils/adt/jsonpath_scan.l b/src/backend/utils/adt/jsonpath_scan.l
index e4fadcc2e69..f94074fe342 100644
--- a/src/backend/utils/adt/jsonpath_scan.l
+++ b/src/backend/utils/adt/jsonpath_scan.l
@@ -438,6 +438,7 @@ static const JsonPathKeyword keywords[] = {
 	{8, false, DATETIME_P, "datetime"},
 	{8, false, KEYVALUE_P, "keyvalue"},
 	{9, false, TIMESTAMP_P, "timestamp"},
+	{9, false, STR_TRANSLATE_P, "translate"},
 	{10, false, LIKE_REGEX_P, "like_regex"},
 	{10, false, STR_SPLIT_PART_P, "split_part"},
 	{12, false, TIMESTAMP_TZ_P, "timestamp_tz"},
diff --git a/src/include/utils/jsonpath.h b/src/include/utils/jsonpath.h
index 8d27206e242..c2c95a9b8a5 100644
--- a/src/include/utils/jsonpath.h
+++ b/src/include/utils/jsonpath.h
@@ -123,6 +123,7 @@ typedef enum JsonPathItemType
 	jpiStrBtrim,				/* .btrim() item method */
 	jpiStrInitcap,				/* .initcap() item method */
 	jpiStrSplitPart,			/* .split_part() item method */
+	jpiStrTranslate,			/* .translate() item method */
 } JsonPathItemType;
 
 /* XQuery regex mode flags for LIKE_REGEX predicate */
diff --git a/src/test/regress/expected/jsonb_jsonpath.out b/src/test/regress/expected/jsonb_jsonpath.out
index afa6c4cb529..b04b01f716e 100644
--- a/src/test/regress/expected/jsonb_jsonpath.out
+++ b/src/test/regress/expected/jsonb_jsonpath.out
@@ -3060,6 +3060,48 @@ select jsonb_path_query('"hello world"', '$.replace("hello","bye") starts with "
  true
 (1 row)
 
+-- Test .translate()
+select jsonb_path_query('null', '$.translate("x", "bye")');
+ERROR:  jsonpath item method .translate() can only be applied to a string
+select jsonb_path_query('null', '$.translate("x", "bye")', silent => true);
+ jsonb_path_query 
+------------------
+(0 rows)
+
+select jsonb_path_query('["x", "y", "z"]', '$.translate("x", "bye")');
+ jsonb_path_query 
+------------------
+ "b"
+ "y"
+ "z"
+(3 rows)
+
+select jsonb_path_query('{}', '$.translate("x", "bye")');
+ERROR:  jsonpath item method .translate() can only be applied to a string
+select jsonb_path_query('[]', 'strict $.translate("x", "bye")', silent => true);
+ jsonb_path_query 
+------------------
+(0 rows)
+
+select jsonb_path_query('{}', '$.translate("x", "bye")', silent => true);
+ jsonb_path_query 
+------------------
+(0 rows)
+
+select jsonb_path_query('1.23', '$.translate("x", "bye")');
+ERROR:  jsonpath item method .translate() can only be applied to a string
+select jsonb_path_query('"hello world"', '$.translate("hello","bye")');
+ jsonb_path_query 
+------------------
+ "byee wred"
+(1 row)
+
+select jsonb_path_query('"hello world"', '$.translate("hello","bye") starts with "bye"');
+ jsonb_path_query 
+------------------
+ true
+(1 row)
+
 -- Test .split_part()
 select jsonb_path_query('"abc~@~def~@~ghi"', '$.split_part("~@~", 2)');
  jsonb_path_query 
diff --git a/src/test/regress/expected/jsonpath.out b/src/test/regress/expected/jsonpath.out
index ea971e79854..86a10ff3eab 100644
--- a/src/test/regress/expected/jsonpath.out
+++ b/src/test/regress/expected/jsonpath.out
@@ -441,6 +441,12 @@ select '$.replace("hello","bye")'::jsonpath;
  $.replace("hello","bye")
 (1 row)
 
+select '$.translate("hello","bye")'::jsonpath;
+          jsonpath          
+----------------------------
+ $.translate("hello","bye")
+(1 row)
+
 select '$.lower()'::jsonpath;
  jsonpath  
 -----------
diff --git a/src/test/regress/expected/sqljson_queryfuncs.out b/src/test/regress/expected/sqljson_queryfuncs.out
index 57e52e963f6..7940faa58ba 100644
--- a/src/test/regress/expected/sqljson_queryfuncs.out
+++ b/src/test/regress/expected/sqljson_queryfuncs.out
@@ -1279,6 +1279,7 @@ CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.lower()'));
 CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.upper()'));
 CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.initcap()'));
 CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.replace("hello", "bye")'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.translate("hello", "bye")'));
 CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.split_part(",", 2)'));
 -- DEFAULT expression
 CREATE OR REPLACE FUNCTION ret_setint() RETURNS SETOF integer AS
diff --git a/src/test/regress/sql/jsonb_jsonpath.sql b/src/test/regress/sql/jsonb_jsonpath.sql
index d3a38c57791..5938b819284 100644
--- a/src/test/regress/sql/jsonb_jsonpath.sql
+++ b/src/test/regress/sql/jsonb_jsonpath.sql
@@ -718,6 +718,17 @@ select jsonb_path_query('1.23', '$.replace("x", "bye")');
 select jsonb_path_query('"hello world"', '$.replace("hello","bye")');
 select jsonb_path_query('"hello world"', '$.replace("hello","bye") starts with "bye"');
 
+-- Test .translate()
+select jsonb_path_query('null', '$.translate("x", "bye")');
+select jsonb_path_query('null', '$.translate("x", "bye")', silent => true);
+select jsonb_path_query('["x", "y", "z"]', '$.translate("x", "bye")');
+select jsonb_path_query('{}', '$.translate("x", "bye")');
+select jsonb_path_query('[]', 'strict $.translate("x", "bye")', silent => true);
+select jsonb_path_query('{}', '$.translate("x", "bye")', silent => true);
+select jsonb_path_query('1.23', '$.translate("x", "bye")');
+select jsonb_path_query('"hello world"', '$.translate("hello","bye")');
+select jsonb_path_query('"hello world"', '$.translate("hello","bye") starts with "bye"');
+
 -- Test .split_part()
 select jsonb_path_query('"abc~@~def~@~ghi"', '$.split_part("~@~", 2)');
 select jsonb_path_query('"abc,def,ghi,jkl"', '$.split_part(",", -2)');
diff --git a/src/test/regress/sql/jsonpath.sql b/src/test/regress/sql/jsonpath.sql
index 44178d8b45a..bce435a6301 100644
--- a/src/test/regress/sql/jsonpath.sql
+++ b/src/test/regress/sql/jsonpath.sql
@@ -79,6 +79,7 @@ select '$.date()'::jsonpath;
 select '$.decimal(4,2)'::jsonpath;
 select '$.string()'::jsonpath;
 select '$.replace("hello","bye")'::jsonpath;
+select '$.translate("hello","bye")'::jsonpath;
 select '$.lower()'::jsonpath;
 select '$.upper()'::jsonpath;
 select '$.lower().upper().lower().replace("hello","bye")'::jsonpath;
diff --git a/src/test/regress/sql/sqljson_queryfuncs.sql b/src/test/regress/sql/sqljson_queryfuncs.sql
index d218b44ea47..7521b37dae5 100644
--- a/src/test/regress/sql/sqljson_queryfuncs.sql
+++ b/src/test/regress/sql/sqljson_queryfuncs.sql
@@ -409,6 +409,7 @@ CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.lower()'));
 CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.upper()'));
 CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.initcap()'));
 CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.replace("hello", "bye")'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.translate("hello", "bye")'));
 CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.split_part(",", 2)'));
 
 -- DEFAULT expression
-- 
2.53.0