CODEWITHSUNDEEP
jsonoracle-database
Bhawana Solanki
Bhawana Solanki

Reputation: 55

Append in nested array JSON object in oracle

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.

Upvotes: 0

Views: 886

Answers (2)

Bhawana Solanki
Bhawana Solanki

Reputation: 55

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

Upvotes: 0

p3consulting
p3consulting

Reputation: 4595

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

Upvotes: 2

Related Questions