This is a spur of the moment patch really, but while going through the process of translating some json data from Greek to English I found myself wishing for $subject. Inspired by the Unix tr utility.
Here's a working v1 If others find it useful as well, I'd go ahead with a json implementation And an additional parameter to make in-arrays replacement optional.
From c292e634a465fa178a87e711c7b47668afd3091e Mon Sep 17 00:00:00 2001 From: Florents Tselai <[email protected]> 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
