Oracle json_transform replace particular value on any level

Question

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

p3consulting · Accepted Answer · 2022-09-14 14:37:54Z

1

Have you seen that even

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

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

answered Sep 14, 2022 at 14:37

p3consulting

5,4312 gold badges14 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

art Over a year ago

Yes, so thats why i'm afraid .. (two dots) may not work for transform. But there is nothing in oracle documentation about limits for json path for transform!

p3consulting Over a year ago

for the fun you may try , 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 since json_transform supports several expressions at once.

art Over a year ago

'$.*.locationId?(@ == "000")' = '111' doesn't work either. Only strict path like '$.a.locationId?(@ == "000")' = '111' works. Very limited functionality from Oracle..

p3consulting · Accepted Answer · 2022-09-14 15:43:41Z

1

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"}}}}

answered Sep 14, 2022 at 15:43

p3consulting

5,4312 gold badges14 silver badges15 bronze badges

2 Comments

art Over a year ago

This doesn't work for me in Oracle 19c. Only strict path without * works

p3consulting Over a year ago

The latest I posted shows that the behavior also changes according to the content of the sibling nodes. So the best may be to post a bug report, unless somebody else can better explain why.

Collectives™ on Stack Overflow

Oracle json_transform replace particular value on any level

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related