Re: Add jsonb_translate(jsonb, from, to)

Thanks for taking the time Evan

On Sun, Sep 28, 2025, 12:34 Chao Li <li(dot)evan(dot)chao(at)gmail(dot)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.

>
> On Sep 28, 2025, at 01:16, Florents Tselai <florents(dot)tselai(at)gmail(dot)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/
>
>
>
>
>