Thread

Re: Add jsonb_translate(jsonb, from, to)

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

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