v1-0001-Add-jsonb_translate-function.patch

From c292e634a465fa178a87e711c7b47668afd3091e Mon Sep 17 00:00:00 2001
From: Florents Tselai <florents.tselai@gmail.com>
Date: Sat, 27 Sep 2025 20:02:44 +0300
Subject: [PATCH v1] Add jsonb_translate function

Introduce a new function jsonb_translate(jsonb, from text, to text) that recursively replaces string values in json
documents.
---
 doc/src/sgml/func/func-json.sgml         | 19 ++++++++++
 src/backend/catalog/system_functions.sql |  7 ++++
 src/backend/utils/adt/jsonb.c            | 45 ++++++++++++++++++++++++
 src/include/catalog/pg_proc.dat          |  3 ++
 src/test/regress/expected/jsonb.out      | 32 +++++++++++++++++
 src/test/regress/sql/jsonb.sql           |  8 +++++
 6 files changed, 114 insertions(+)

diff --git a/doc/src/sgml/func/func-json.sgml b/doc/src/sgml/func/func-json.sgml
index 91f98a345d4..469877dd8c5 100644
--- a/doc/src/sgml/func/func-json.sgml
+++ b/doc/src/sgml/func/func-json.sgml
@@ -1845,6 +1845,25 @@ 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>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 JSONB 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..4f2eb18f713 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -621,6 +621,13 @@ 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';
+
 -- default normalization form is NFC, per SQL standard
 CREATE OR REPLACE FUNCTION
   "normalize"(text, text DEFAULT 'NFC')
diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index da94d424d61..33b55b67ce9 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,47 @@ JsonbUnquote(Jsonb *jb)
 	else
 		return JsonbToCString(NULL, &jb->root, VARSIZE(jb));
 }
+typedef struct
+{
+	text    *from;
+	text    *to;
+	Oid     collation;
+	bool    with_keys;
+} 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);
+	bool    with_keys = PG_GETARG_BOOL(3);
+	Oid     collation = PG_GET_COLLATION();
+	Jsonb   *res;
+
+	TranslateState *state = palloc0(sizeof(TranslateState));
+	state->from = from;
+	state->to = to;
+	state->collation = collation;
+	state->with_keys = with_keys;
+
+	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..91f4f70da7f 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -9473,6 +9473,9 @@
 { 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' },
 
 # uuid
 { oid => '2952', descr => 'I/O',
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/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