Re: Add jsonb_translate(jsonb, from, to)

> On 28 Sep 2025, at 2:26 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>
>
>
> ne 28. 9. 2025 v 12:11 odesílatel Florents Tselai <florents(dot)tselai(at)gmail(dot)com <mailto:florents(dot)tselai(at)gmail(dot)com>> napsal:
>> Thanks for taking the time Evan
>>
>> On Sun, Sep 28, 2025, 12:34 Chao Li <li(dot)evan(dot)chao(at)gmail(dot)com <mailto: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.
>
> 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.