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