Thread

Re: Add jsonb_translate(jsonb, from, to)

Florents Tselai <florents.tselai@gmail.com> — 2025-09-29T12:34:18Z

> 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.