By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
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"}
}'
)
1 rows affected
COMMIT;
UPDATE table_name
SET data = JSON_MERGEPATCH(data, '{"name":{"value": "XYZ"}}')
1 rows affected
SELECT * FROM table_name;
| DATA |
|---|
| {"app":{"value":"ff5aeb05-7a22-46fd"},"name":{"value":"XYZ"},"surname":{"value":"Smith"}} |
ROLLBACK;
UPDATE table_name
SET data = JSON_TRANSFORM(data, set '$.name.value' = 'XYZ')
1 rows affected
SELECT * FROM table_name;
| DATA |
|---|
| {"app":{"value":"ff5aeb05-7a22-46fd"},"name":{"value":"XYZ"},"surname":{"value":"Smith"}} |
ROLLBACK;
UPDATE table_name
SET data = REGEXP_REPLACE(
data,
'("name"\s*:\s*\{\s*"value"\s*:\s*")(.*?)("\s*\})',
'\1XYZ\3'
)
1 rows affected
SELECT * FROM table_name;
| DATA |
|---|
| { "app":{"value": "ff5aeb05-7a22-46fd"}, "name": {"value": "XYZ"}, "surname": {"value": "Smith"} } |