Append in nested array JSON object in oracle

Question

I have JSON document column in one of the table and its structure is like:-

{ 
 "root":[{"MCR":"MCR_1",
          "MCR_COLUMNS":{ 
                           "MCR_COLUMN_1":"ABC1",
                           "MCR_COLUMN_2":"ABC2"
                        }
            },
         {"MCR":"MCR_2",
          "MCR_COLUMNS":{ 
                            "MCR_COLUMN_1":"XYZ1",
                             "MCR_COLUMN_2":"XYZ2"
                        }
            }
        ]
}

Now I want to write a merge statement to merge in this document to manage two cases

CASE-1) If MCR value is already present in document, then directly append MCR_COLUMN_x and its value to JSON object of its MCR_COLUMNS. eg. I want to append

{"MCR":"MCR_1",
          "MCR_COLUMNS":{ 
                           "MCR_COLUMN_3":"ABC3"
                        }
            }

so, the updated document should be

{ 
 "root":[{"MCR":"MCR_1",
          "MCR_COLUMNS":{ 
                           "MCR_COLUMN_1":"ABC1",
                           "MCR_COLUMN_2":"ABC2",
                           "MCR_COLUMN_3":"ABC3"
                        }
            },
         {"MCR":"MCR_2",
          "MCR_COLUMNS":{ 
                            "MCR_COLUMN_1":"XYZ1",
                             "MCR_COLUMN_2":"XYZ2"
                        }
            }
        ]
}

CASE-2) If MCR value does not exist then it appends a new JSON object into the root array. for eg: if i want to append

{"MCR":"MCR_3",
          "MCR_COLUMNS":{ 
                           "MCR_COLUMN_1":"UVW1",
                           "MCR_COLUMN_2":"UVW2"
                        }
            }

then updated document should be

{ 
 "root":[{"MCR":"MCR_1",
          "MCR_COLUMNS":{ 
                           "MCR_COLUMN_1":"ABC1",
                           "MCR_COLUMN_2":"ABC2"
                        }
            },
         {"MCR":"MCR_2",
          "MCR_COLUMNS":{ 
                            "MCR_COLUMN_1":"XYZ1",
                            "MCR_COLUMN_2":"XYZ2"
                        }
            },
         {"MCR":"MCR_3",
          "MCR_COLUMNS":{ 
                           "MCR_COLUMN_1":"UVW1",
                           "MCR_COLUMN_2":"UVW2"
                        }
            }
        ]
}

I had tried JSON_mergepatch and JSON_Transform but case-1 I'm not able to achieve. And since I'll not have before-hand knowledge whether MCR is already present or not, I just can not only right solution for case-2. Any help or suggestion will be very much appreciated.

p3consulting · Accepted Answer · 2023-01-16 13:58:57Z

2

To check if the MCR value exists:

WHERE json_exists(json_value, '$?(@.root[*].MCR == "MCR_1")')

To add an item to MCR_COLUMNS

update test_js
set json_value = json_transform(
    json_value,
    INSERT '$.root.MCR_COLUMNS.MCR_COLUMN_3' = 'ABC3'
)
where json_exists(json_value, '$?(@.root[*].MCR == "MCR_1")')
;

To add an item to root array:

update test_js
set json_value = json_transform(
    json_value,
    APPEND '$.root' = '{"MCR":"MCR_3", "MCR_COLUMNS":{  "MCR_COLUMN_1":"UVW1", "MCR_COLUMN_2":"UVW2" } }' FORMAT JSON
)
where not json_exists(json_value, '$?(@.root[*].MCR == "MCR_3")')
;

answered Jan 16, 2023 at 13:58

p3consulting

5,4212 gold badges14 silver badges15 bronze badges

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

6 Comments

Bhawana Solanki Over a year ago

This throws error to me in 19c SQL Error: ORA-00936: missing expression

Bhawana Solanki Over a year ago

Looks like json_transform is not available in 19c, is there a way to resolve it in 19c version?

p3consulting Over a year ago

Under 19c, either you use the JSON types in PL/SQL and write your own function, you can do an update from a select where you update the json with the value rebuild from the original + the one you want to add... if the conditions are met (you will need json_object, json_arrayagg, json_exists, json_value)... probably 3 to 4 levels of select to achieve it, ... kinda brute force technique but should work

Bhawana Solanki Over a year ago

The above solution for INSERT, it inserts into all json objects of root array, instead it should only insert to that json object which matches the MCR value. For eg. when I tried inserting "MCR_COLUMN_4" : "ABC4" for MCR="MCR_1" It gave me below output which is not desired. ``` { "root" : [ { "MCR" : "MCR_1", "MCR_COLUMNS" : { "MCR_COLUMN_1" : "ABC1", "MCR_COLUMN_2" : "ABC2", "MCR_COLUMN_3" : "ABC3", "MCR_COLUMN_4" : "ABC4" } }, { "MCR" : "MCR_2", "MCR_COLUMNS" : { "MCR_COLUMN_1" : "UVW1", "MCR_COLUMN_2" : "UVW2", "MCR_COLUMN_4" : "ABC4" } } ] } ```

Bhawana Solanki Over a year ago

I was able to resolve by further writing condition in INSERT statement. ``` update test_js set json_value = json_transform( json_value, INSERT '$.root[*]?(@.MCR=="MCR_1").MCR_COLUMNS.MCR_COLUMN_4' = 'ABC4') ) where json_exists(json_value, '$?(@.root[*].MCR == "MCR_1")') ; ```

|

Bhawana Solanki · Accepted Answer · 2023-01-19 09:36:28Z

0

In addition to @p3consulting's answer, to insert into a particular array element only(here in this case 'MCR_1') further conditions can be applied to INSERT.

update test_js 
set json_value = json_transform( json_value, INSERT '$.root[*]?(@.MCR=="MCR_1").MCR_COLUMNS.MCR_COLUMN_4' = 'ABC4') ) 
where json_exists(json_value, '$?(@.root[*].MCR == "MCR_1")') ;

answered Jan 19, 2023 at 9:36

Bhawana Solanki

557 bronze badges

Collectives™ on Stack Overflow

Append in nested array JSON object in oracle

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related