v2-0001-Add-json-b-_translate-function.patch
application/octet-stream
Filename: v2-0001-Add-json-b-_translate-function.patch
Type: application/octet-stream
Part: 0
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