Add jsonb_translate(jsonb, from, to)

From 01620adc751a1d0cabefccd3f1fc79237dae5ba1 Mon Sep 17 00:00:00 2001
From: Florents Tselai <florents.tselai@gmail.com>
Date: Mon, 29 Sep 2025 20:12:09 +0300
Subject: [PATCH v2] Add json{b}_translate function

Introduce a new function json{b}_translate(json{b}, from text, to text) that recursively replaces string values in json
documents. Recursively means that both string values and string array elements are replaced.
---
 doc/src/sgml/func/func-json.sgml         | 26 ++++++++++++++
 src/backend/catalog/system_functions.sql | 14 ++++++++
 src/backend/utils/adt/json.c             | 43 ++++++++++++++++++++++++
 src/backend/utils/adt/jsonb.c            | 41 ++++++++++++++++++++++
 src/include/catalog/pg_proc.dat          |  6 ++++
 src/test/regress/expected/json.out       | 32 ++++++++++++++++++
 src/test/regress/expected/jsonb.out      | 32 ++++++++++++++++++
 src/test/regress/sql/json.sql            |  8 +++++
 src/test/regress/sql/jsonb.sql           |  8 +++++
 9 files changed, 210 insertions(+)

diff --git a/doc/src/sgml/func/func-json.sgml b/doc/src/sgml/func/func-json.sgml
index 91f98a345d4..fa93c0fab3f 100644
--- a/doc/src/sgml/func/func-json.sgml
+++ b/doc/src/sgml/func/func-json.sgml
@@ -1845,6 +1845,32 @@ ERROR:  value too long for type character(2)
         <returnvalue>t</returnvalue>
        </para></entry>
       </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>json_translate</primary>
+        </indexterm>
+        <function>json_translate</function> ( <type>jsonb</type>, <type>text</type>, <type>text</type> )
+        <returnvalue>json</returnvalue>
+       </para>
+        <para role="func_signature">
+        <indexterm>
+         <primary>jsonb_translate</primary>
+        </indexterm>
+        <function>jsonb_translate</function> ( <type>jsonb</type>, <type>text</type>, <type>text</type> )
+        <returnvalue>jsonb</returnvalue>
+       </para>
+        <para>
+         Recursively replaces string values in a document that exactly match
+         the second argument with the third argument.
+        </para>
+        <para>
+         <literal>jsonb_translate('{"message": "world", "elements": ["world", "orange"]}', 'world', 'earth')</literal>
+         <returnvalue>{"message": "earth", "elements": ["earth", "orange"]}</returnvalue>
+        </para>
+       </entry>
+      </row>
      </tbody>
     </tgroup>
    </table>
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 2d946d6d9e9..caf9919f8fd 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -621,6 +621,20 @@ LANGUAGE INTERNAL
 STRICT STABLE PARALLEL SAFE
 AS 'json_strip_nulls';
 
+CREATE OR REPLACE FUNCTION
+    jsonb_translate(target jsonb, from_ text, to_ text)
+    RETURNS jsonb
+    LANGUAGE INTERNAL
+    STRICT STABLE PARALLEL SAFE
+AS 'jsonb_translate';
+
+CREATE OR REPLACE FUNCTION
+    json_translate(target json, from_ text, to_ text)
+    RETURNS json
+    LANGUAGE INTERNAL
+    STRICT STABLE PARALLEL SAFE
+AS 'json_translate';
+
 -- default normalization form is NFC, per SQL standard
 CREATE OR REPLACE FUNCTION
   "normalize"(text, text DEFAULT 'NFC')
diff --git a/src/backend/utils/adt/json.c b/src/backend/utils/adt/json.c
index e9d370cb3da..f6c078ac304 100644
--- a/src/backend/utils/adt/json.c
+++ b/src/backend/utils/adt/json.c
@@ -29,6 +29,7 @@
 #include "utils/jsonfuncs.h"
 #include "utils/lsyscache.h"
 #include "utils/typcache.h"
+#include "utils/varlena.h"
 
 
 /*
@@ -1911,3 +1912,45 @@ json_typeof(PG_FUNCTION_ARGS)
 
 	PG_RETURN_TEXT_P(cstring_to_text(type));
 }
+
+typedef struct
+{
+	text    *from;
+	text    *to;
+	Oid     collation;
+} TranslateState;
+
+static text *
+translate_json_string_action(void *vstate, char *elem_value, int elem_len)
+{
+	TranslateState *state = (TranslateState *) vstate;
+
+	char   *from_str = VARDATA_ANY(state->from);
+	int     from_len = VARSIZE_ANY_EXHDR(state->from);
+
+	if (varstr_cmp(elem_value, elem_len,
+				   from_str, from_len,
+				   state->collation) == 0)
+		return state->to;
+
+	return cstring_to_text_with_len(elem_value, elem_len);
+}
+
+Datum
+json_translate(PG_FUNCTION_ARGS)
+{
+	text    *json = PG_GETARG_TEXT_PP(0);
+	text    *from = PG_GETARG_TEXT_PP(1);
+	text    *to = PG_GETARG_TEXT_PP(2);
+	text    *res;
+
+	TranslateState *state = palloc0(sizeof(TranslateState));
+	state->from = from;
+	state->to = to;
+	state->collation = PG_GET_COLLATION();
+
+	res = transform_json_string_values(json, state,
+									   translate_json_string_action);
+
+	PG_RETURN_TEXT_P(res);
+}
diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index da94d424d61..b0aac326df9 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -24,6 +24,7 @@
 #include "utils/jsonfuncs.h"
 #include "utils/lsyscache.h"
 #include "utils/typcache.h"
+#include "utils/varlena.h"
 
 typedef struct JsonbInState
 {
@@ -2252,3 +2253,43 @@ JsonbUnquote(Jsonb *jb)
 	else
 		return JsonbToCString(NULL, &jb->root, VARSIZE(jb));
 }
+typedef struct
+{
+	text    *from;
+	text    *to;
+	Oid     collation;
+} TranslateState;
+
+static text *
+translate_jsonb_string_action(void *vstate, char *elem_value, int elem_len)
+{
+	TranslateState *state = (TranslateState *) vstate;
+
+	char   *from_str = VARDATA_ANY(state->from);
+	int     from_len = VARSIZE_ANY_EXHDR(state->from);
+
+	if (varstr_cmp(elem_value, elem_len,
+				   from_str, from_len,
+				   state->collation) == 0)
+		return state->to;
+
+	return cstring_to_text_with_len(elem_value, elem_len);
+}
+
+Datum
+jsonb_translate(PG_FUNCTION_ARGS)
+{
+	Jsonb   *jb = PG_GETARG_JSONB_P(0);
+	text    *from = PG_GETARG_TEXT_PP(1);
+	text    *to = PG_GETARG_TEXT_PP(2);
+	Jsonb   *res;
+
+	TranslateState *state = palloc0(sizeof(TranslateState));
+	state->from = from;
+	state->to = to;
+	state->collation = PG_GET_COLLATION();;
+
+	res = transform_jsonb_string_values(jb, state, translate_jsonb_string_action);
+
+	PG_RETURN_JSONB_P(res);
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 01eba3b5a19..c1d837c0594 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -9473,6 +9473,12 @@
 { oid => '3968', descr => 'get the type of a json value',
   proname => 'json_typeof', prorettype => 'text', proargtypes => 'json',
   prosrc => 'json_typeof' },
+{ oid => '4175', descr => 'replace recursively json string values',
+  proname => 'jsonb_translate', prorettype => 'jsonb', proargtypes => 'jsonb text text',
+  prosrc => 'jsonb_translate' },
+{ oid => '4176', descr => 'replace recursively json string values',
+  proname => 'json_translate', prorettype => 'json', proargtypes => 'json text text',
+  prosrc => 'json_translate' },
 
 # uuid
 { oid => '2952', descr => 'I/O',
diff --git a/src/test/regress/expected/json.out b/src/test/regress/expected/json.out
index 04b478cb468..3be687fb47a 100644
--- a/src/test/regress/expected/json.out
+++ b/src/test/regress/expected/json.out
@@ -2766,3 +2766,35 @@ select ts_headline('[]'::json, tsquery('aaa & bbb'));
  []
 (1 row)
 
+-- json_translate
+select json_translate('{"message": "world"}', 'world', 'earth'); -- basic case
+   json_translate    
+---------------------
+ {"message":"earth"}
+(1 row)
+
+select json_translate('"hello world"', 'world', 'earth'); -- shouldn't change
+ json_translate 
+----------------
+ "hello world"
+(1 row)
+
+select json_translate('"hello world"', 'hello world', 'hello earth'); -- this should
+ json_translate 
+----------------
+ "hello earth"
+(1 row)
+
+select json_translate('{"message": "world", "elements": ["world", "orange"]}', 'world', 'earth'); -- in arrays
+                  json_translate                   
+---------------------------------------------------
+ {"message":"earth","elements":["earth","orange"]}
+(1 row)
+
+-- should *not* touch keys by default
+select json_translate('{"world": "ok"}', 'world', 'earth');
+ json_translate 
+----------------
+ {"world":"ok"}
+(1 row)
+
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 5a1eb18aba2..5ad82f5ff3a 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -5831,3 +5831,35 @@ select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8;
  12345
 (1 row)
 
+-- jsonb_translate
+select jsonb_translate('{"message": "world"}', 'world', 'earth'); -- basic case
+   jsonb_translate    
+----------------------
+ {"message": "earth"}
+(1 row)
+
+select jsonb_translate('"hello world"', 'world', 'earth'); -- shouldn't change
+ jsonb_translate 
+-----------------
+ "hello world"
+(1 row)
+
+select jsonb_translate('"hello world"', 'hello world', 'hello earth'); -- this should
+ jsonb_translate 
+-----------------
+ "hello earth"
+(1 row)
+
+select jsonb_translate('{"message": "world", "elements": ["world", "orange"]}', 'world', 'earth'); -- in arrays
+                    jsonb_translate                    
+-------------------------------------------------------
+ {"message": "earth", "elements": ["earth", "orange"]}
+(1 row)
+
+-- should *not* touch keys by default
+select jsonb_translate('{"world": "ok"}', 'world', 'earth');
+ jsonb_translate 
+-----------------
+ {"world": "ok"}
+(1 row)
+
diff --git a/src/test/regress/sql/json.sql b/src/test/regress/sql/json.sql
index e9800b21ffe..7714ae1a99f 100644
--- a/src/test/regress/sql/json.sql
+++ b/src/test/regress/sql/json.sql
@@ -888,3 +888,11 @@ select ts_headline('english', '{"a": "aaa bbb", "b": {"c": "ccc ddd fff", "c1":
 select ts_headline('null'::json, tsquery('aaa & bbb'));
 select ts_headline('{}'::json, tsquery('aaa & bbb'));
 select ts_headline('[]'::json, tsquery('aaa & bbb'));
+
+-- json_translate
+select json_translate('{"message": "world"}', 'world', 'earth'); -- basic case
+select json_translate('"hello world"', 'world', 'earth'); -- shouldn't change
+select json_translate('"hello world"', 'hello world', 'hello earth'); -- this should
+select json_translate('{"message": "world", "elements": ["world", "orange"]}', 'world', 'earth'); -- in arrays
+-- should *not* touch keys by default
+select json_translate('{"world": "ok"}', 'world', 'earth');
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index 57c11acddfe..03114673883 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -1590,3 +1590,11 @@ select '12345.0000000000000000000000000000000000000000000005'::jsonb::float8;
 select '12345.0000000000000000000000000000000000000000000005'::jsonb::int2;
 select '12345.0000000000000000000000000000000000000000000005'::jsonb::int4;
 select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8;
+
+-- jsonb_translate
+select jsonb_translate('{"message": "world"}', 'world', 'earth'); -- basic case
+select jsonb_translate('"hello world"', 'world', 'earth'); -- shouldn't change
+select jsonb_translate('"hello world"', 'hello world', 'hello earth'); -- this should
+select jsonb_translate('{"message": "world", "elements": ["world", "orange"]}', 'world', 'earth'); -- in arrays
+-- should *not* touch keys by default
+select jsonb_translate('{"world": "ok"}', 'world', 'earth');
-- 
2.49.0