Add jsonb_translate(jsonb, from, to)

Hi Florents,

Thanks for the patch. I once had the same pain on a similar task, I had to create a PL/SQL function at the time.

I haven’t read the code change yet, but I think the function name jsonb_translate() sounds to generic. To make the name more meaningful, I would suggest a few candidates: jsonb_replace_text(), or jsonb_replace_value(), or jsonb_deep_replace().

Also, I want to understand why do you decide to support only whole word matching?

```
evantest=# select jsonb_translate('{"message": "world"}', 'wor', 'earth');
jsonb_translate
----------------------
{"message": "world"}
(1 row)
```

With this patch, partial match will not result in a replacement.

On Sep 28, 2025, at 01:16, Florents Tselai <florents.tselai@gmail.com> wrote:

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.
<v1-0001-Add-jsonb_translate-function.patch>

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

Thanks for taking the time Evan

On Sun, Sep 28, 2025, 12:34 Chao Li <li.evan.chao@gmail.com> wrote:

Hi Florents,

Thanks for the patch. I once had the same pain on a similar task, I had to
create a PL/SQL function at the time.

I haven’t read the code change yet, but I think the function name
jsonb_translate() sounds to generic. To make the name more meaningful, I
would suggest a few candidates: jsonb_replace_text(), or
jsonb_replace_value(), or jsonb_deep_replace().

Also, I want to understand why do you decide to support only whole word
matching?

```
evantest=# select jsonb_translate('{"message": "world"}', 'wor', 'earth');
jsonb_translate
----------------------
{"message": "world"}
(1 row)
```

With this patch, partial match will not result in a replacement.

That is on purpose. My use case for this is to replace categorical/enum
values scattered deep inside the json structure.
Hence the name translate which usually means mapping from one key space
to another.

Partial replacement wasn't the case for me, and most importantly I guess I
could achieve the same by casting to text replacing and casting back to
jsonb.

ne 28. 9. 2025 v 12:11 odesílatel Florents Tselai <florents.tselai@gmail.com>
napsal:

Thanks for taking the time Evan

On Sun, Sep 28, 2025, 12:34 Chao Li <li.evan.chao@gmail.com> wrote:

Hi Florents,

Thanks for the patch. I once had the same pain on a similar task, I had
to create a PL/SQL function at the time.

I haven’t read the code change yet, but I think the function name
jsonb_translate() sounds to generic. To make the name more meaningful, I
would suggest a few candidates: jsonb_replace_text(), or
jsonb_replace_value(), or jsonb_deep_replace().

Also, I want to understand why do you decide to support only whole word
matching?

```
evantest=# select jsonb_translate('{"message": "world"}', 'wor', 'earth');
jsonb_translate
----------------------
{"message": "world"}
(1 row)
```

With this patch, partial match will not result in a replacement.

That is on purpose. My use case for this is to replace categorical/enum
values scattered deep inside the json structure.
Hence the name translate which usually means mapping from one key space
to another.

Partial replacement wasn't the case for me, and most importantly I guess I
could achieve the same by casting to text replacing and casting back to
jsonb.

Cannot be better to use JsonPath for specification what should be replaced?

Regards

Pavel

On 28 Sep 2025, at 2:26 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

ne 28. 9. 2025 v 12:11 odesílatel Florents Tselai <florents.tselai@gmail.com <mailto:florents.tselai@gmail.com>> napsal:

Thanks for taking the time Evan

On Sun, Sep 28, 2025, 12:34 Chao Li <li.evan.chao@gmail.com <mailto:li.evan.chao@gmail.com>> wrote:

Hi Florents,

Thanks for the patch. I once had the same pain on a similar task, I had to create a PL/SQL function at the time.

I haven’t read the code change yet, but I think the function name jsonb_translate() sounds to generic. To make the name more meaningful, I would suggest a few candidates: jsonb_replace_text(), or jsonb_replace_value(), or jsonb_deep_replace().

Also, I want to understand why do you decide to support only whole word matching?

```
evantest=# select jsonb_translate('{"message": "world"}', 'wor', 'earth');
jsonb_translate
----------------------
{"message": "world"}
(1 row)
```

With this patch, partial match will not result in a replacement.

That is on purpose. My use case for this is to replace categorical/enum values scattered deep inside the json structure.
Hence the name translate which usually means mapping from one key space to another.

Partial replacement wasn't the case for me, and most importantly I guess I could achieve the same by casting to text replacing and casting back to jsonb.

Cannot be better to use JsonPath for specification what should be replaced?

Fair point.
The main purpose of this patch is to provide a recursive, global replacement across all values and arrays,
which is not as straightforward to express in JSONPath today.
I understand that some may find this too case-specific, so I’m just leaving it out there for consideration.
That said, I believe it can be quite useful in domains where documents carry many tags or labels that need to be translated or normalized consistently.

On Mon, Sep 29, 2025 at 3:34 PM Florents Tselai <florents.tselai@gmail.com>
wrote:

On 28 Sep 2025, at 2:26 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

ne 28. 9. 2025 v 12:11 odesílatel Florents Tselai <
florents.tselai@gmail.com> napsal:

Thanks for taking the time Evan

On Sun, Sep 28, 2025, 12:34 Chao Li <li.evan.chao@gmail.com> wrote:

Hi Florents,

Thanks for the patch. I once had the same pain on a similar task, I had
to create a PL/SQL function at the time.

I haven’t read the code change yet, but I think the function name
jsonb_translate() sounds to generic. To make the name more meaningful, I
would suggest a few candidates: jsonb_replace_text(), or
jsonb_replace_value(), or jsonb_deep_replace().

Also, I want to understand why do you decide to support only whole word
matching?

```
evantest=# select jsonb_translate('{"message": "world"}', 'wor',
'earth');
jsonb_translate
----------------------
{"message": "world"}
(1 row)
```

With this patch, partial match will not result in a replacement.

That is on purpose. My use case for this is to replace categorical/enum
values scattered deep inside the json structure.
Hence the name translate which usually means mapping from one key space
to another.

Partial replacement wasn't the case for me, and most importantly I guess
I could achieve the same by casting to text replacing and casting back to
jsonb.

Cannot be better to use JsonPath for specification what should be replaced?

Fair point.
The main purpose of this patch is to provide a recursive, global
replacement across all values and arrays,
which is not as straightforward to express in JSONPath today.
I understand that some may find this too case-specific, so I’m just
leaving it out there for consideration.
That said, I believe it can be quite useful in domains where documents
carry many tags or labels that need to be translated or normalized
consistently.

Here's a v2 with a json_translate implementation for consideration

On 2025-09-27 Sa 1:16 PM, Florents Tselai wrote:

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.

It's not clear to me that this is a good idea. Blindly replacing every
instance of a single string regardless of context seems at best a niche
use. Is there any reason not to make this an extension?

I haven't dug deeply into the patch, but I don't understand why you're
putting an entry for this in system_functions.sql.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com

On 29.09.25 14:34, Florents Tselai wrote:

Cannot be better to use JsonPath for specification what should be
replaced?

Fair point.
The main purpose of this patch is to provide a recursive, global
replacement across all values and arrays,
which is not as straightforward to express in JSONPath today.
I understand that some may find this too case-specific, so I’m just
leaving it out there for consideration.
That said, I believe it can be quite useful in domains where documents
carry many tags or labels that need to be translated or normalized
consistently.

Oracle has a json_transform function, which has also been added to the
SQL standard draft recently. That appears to be more in the direction
that Pavel is suggesting. Maybe someone wants to take a stab at it.
(In that case, having both a json_transform and a json_translate might
be confusing.)