Append in nested array JSON object in oracle-CodePudding

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.

CodePudding user response：

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")')
;