Oracle json_transform replace particular value on any level-CodePudding

I need to replace some values in Oracle 19 column that is CLOB with JSON constraint, but cannot find a right way to do it. Json can be big and I suppose regex function on string may not work due to the length restrictions.

My json may have 'locationId' property at any level with some values. Let's say I have bad value 000 and want to replace it with 111, so everywhere where is "locationId":"000" it becomes "locationId":"111".

I'm trying to use next json_transform command:

SELECT json_transform('
{
 "a": {
   "locationId":"000"
 }, 
 "b": {
   "locationId":"111", 
   "x": {
     "locationId":"000"
    }
 }
}', REPLACE '$..locationId?(@ == "000")' = '111' IGNORE ON MISSING) 
FROM dual

But it returns unchanged json, when next query for the same json fetches values to replace properly :

SELECT json_query('
{
 "a": {
   "locationId":"000"
 }, 
 "b": {
   "locationId":"111", 
   "x": {
     "locationId":"000"
   }
 }
}', '$..locationId?(@ == "000")' WITH WRAPPER) 
FROM dual

Result: ["000","000"]

There is no any documentation or examples how to use filters within json_transform, so I'm not sure it's even possible. Maybe anybody know how to do it? Doesn't matter with json functions or not.

Links I use:

Oracle Json Path Expressions

Oracle json_query

Oracle json_transform

CodePudding user response：

Have you seen that even

REPLACE '$..locationId' = '111' IGNORE ON MISSING

doesn't change anything ? (at least in Cloud 21c)

CodePudding user response：

It's more complicated:

SELECT json_transform('
{
 "locationId" : "000",
 "a": {
   "locationId":"000"
 }, 
 "b": {
   "locationId":"000", 
   "x": {
     "locationId":"000", 
       "y": {
         "locationId":"000"
        }
    }
 }
}'
    , REPLACE '$.locationId?(@ == "000")' = '111' IGNORE ON MISSING
    , REPLACE '$.*.locationId?(@ == "000")' = '111' IGNORE ON MISSING
    , REPLACE '$.*.*.locationId?(@ == "000")' = '111' IGNORE ON MISSING
    , REPLACE '$.*.*.*.locationId?(@ == "000")' = '111' IGNORE ON MISSING
) 
FROM dual;

Gives:

{"locationId":"111","a":{"locationId":"111"},"b":{"locationId":"111","x":{"locationId":"111","y":{"locationId":"111"}}}}