Oracle regexp_replace for JSON value

Question

i would like to find and replace string in JSON.

Example JSON :

{
  "app": {
    "value": "ff5aeb05-7a22-46fd"
  },
  "name": {
    "value": "John"
  },
  "surname": {
    "value": "Smith"
  }
}

Only replace "John" or "Smith" to something "XYZ". Looking for the value of e.g. name. Then replace word after: "value": "John" to "value": "XYZ"

This is my proposal but i would like the solution to be generic.

SELECT regexp_replace('{"app":{"value":"ff5aeb05-7a22-46fd"},"name":{"value":"John"},"surname":{"value":"Smith"}}','John','XYZ')
  FROM dual

Answer 1

A regular expression is the wrong thing to use to modify JSON data (or HTML data). You want to use a proper parser that is able to read the syntax and handle nested objects properly so that when you try to parse:

{"name": {"value": "John"},"surname": {"value": "Smith"}}

and then try to parse:

{"surname": {"value": "Smith"},"name": {"value": "John"}}

(with the keys swapped) you find the correct value attribute in both. And a proper parse will handle duplicate structures found in different branches of the JSON (a regular expression will not know anything about branches of the JSON and will either update the first match it finds, regardless of whether it is in the correct branch or not, or will update all matches, which you probably do not want).

---

You can do it using JSON_MERGEPATCH:

UPDATE table_name
SET data = JSON_MERGEPATCH(data, '{"name":{"value": "XYZ"}}')

or using JSON_TRANSFORM:

UPDATE table_name
SET data = JSON_TRANSFORM(data, set '$.name.value' = 'XYZ')

Which, for the sample data:

CREATE TABLE table_name (data CLOB CHECK(data IS JSON));

INSERT INTO table_name (data) VALUES (
  '{
  "app":{"value": "ff5aeb05-7a22-46fd"},
  "name": {"value": "John"},
  "surname": {"value": "Smith"}
}'
)

After either UPDATE the table contains:

DATA
{"app":{"value":"ff5aeb05-7a22-46fd"},"name":{"value":"XYZ"},"surname":{"value":"Smith"}}

---

If:

• "value" is the only key of the "name" object;
• there are no other "name" keys elsewhere in the JSON that might match;
• the string you are matching has no escaped quotation marks; and
• the value that you are replacing it with is valid as the contents of a JSON string (i.e. anything that may need escaping to be valid within a JSON string has already been escaped).

Then this might work:

UPDATE table_name
SET data = REGEXP_REPLACE(
             data,
             '("name"\s*:\s*\{\s*"value"\s*:\s*")(.*?)("\s*\})',
             '\1XYZ\3'
           )

But that is a lot of "ifs" that you have to assure yourself that you are meeting the conditions for. In general, DO NOT use regular expressions to parse JSON and use a proper parser instead.

fiddle