Quick Tip: json_mergepatch

In honor of #JoelKallmanDay. I wanted to share a quick tip. PL/SQL Object Types for JSON have now been around for a bit, since Oracle Database 12c. Ever since then, this has been the primary way I tend to work with JSON. The main PL/SQL JSON object types are JSON_ELEMENT_T, JSON_OBJECT_T, JSON_ARRAY_T, and JSON_SCALAR_T. Oracle Database 21c has introduced the new JSON data type, but the JSON_OBJECT_T constructor supports the new JSON data type and I still plan to use PL/SQL JSON object types.

One scenario I have ran into more than once is the need to merge two json objects. Maybe we have JSON stored in the DB, and are getting a PUT to update that row and the JSON in that row. Sometimes we need to convert it to JSON_OBJECT_T to handle some business logic before merging. Then we try merging - for example:

p1 = {
    "firstName": "Neal",
    "address": "123 Nevermind Drive",
    "animals": {"dog1":"Fido"}
}

p2 = {
    "firstName": "Neil",
    "lastName": "Fernandez",
    "animals": {"dog2":"Fetch"}
}

/* expected merge output when merging p2 into p1

{
    "firstName": "Neil",
    "lastName": "Fernandez"
    "address": "123 Nevermind Drive",
    "animals":{"dog1":"Fido","dog2":"Fetch"}
}
*/

In the past I have looped through the keys in p2, see if it exists in p1, and if it does we replace it, if it doesn't we add it. Something along the lines of this:

declare
    l_p1 json_object_t;
    l_p2 json_object_t;
    l_p2_key_list  json_key_list;
begin
    -- Assuming you are dealing with JSON_OBJECT_T already
    l_p1 := json_object_t.parse('{
        "firstName": "Neal",
        "address": "123 Nevermind Drive",
        "animals": {"dog1":"Fido"}
    }');
    
    l_p2 := json_object_t.parse('{
        "firstName": "Neil",
        "lastName": "Fernandez",
        "animals": {"dog2":"Fetch"}
    }');
  
    l_p2_key_list := l_p2.get_keys;
    
    << p2_keys_loop >>
    for i in 1 .. l_p2_key_list.count loop
        l_p1.put(l_p2_key_list(i), l_p2.get(l_p2_key_list(i)));   
    end loop;
    
    dbms_output.put_line(l_p1.to_clob);
end;

-- Output
/*
{
    "address":"123 Nevermind Drive",
    "firstName":"Neil",
    "lastName":"Fernandez",
    "animals":{"dog2":"Fetch"}
}
*/

There is one obvious issue though. This is not really a true merge. Take a look at the animals nested object. If there are nested objects or arrays, it actually is just replacing/inserting those objects or arrays with this method and not actually merging. This only works if you do not have nested arrays or objects. There is more work to be done to make this work properly for that scenario.

There is an easier way: JSON_MERGEPATCH

JSON_MERGEPATCH has been the easiest way for me to handle merging JSON and it does an actual merge patch. There is no PL/SQL support for direct assignments, so for now you have to select into from dual and convert your PL/SQL JSON Object types into CLOBs before calling it.

declare
    l_p1 json_object_t;
    l_p2 json_object_t;
    l_p1_clob clob;
    l_p2_clob clob;
    l_merge clob;
    l_merge_json json_object_t;
begin
    -- Assuming you are dealing with JSON_OBJECT_T already
    l_p1 := json_object_t('{
        "firstName": "Neal",
        "address": "123 Nevermind Drive",
        "animals": {"dog1":"Fido"}
    }');
    
    l_p2 := json_object_t('{
        "firstName": "Neil",
        "lastName": "Fernandez",
        "animals": {"dog2":"Fetch"}
    }');
    
    -- Convert to clob to use in SQL
    l_p1_clob := l_p1.to_clob();
    l_p2_clob := l_p2.to_clob();
    
    -- Merge using SQL
    -- There is no PL/SQL support for direct assignments using the JSON_MERGEPATCH function.
    select json_mergepatch(l_p1_clob, l_p2_clob)
    into l_merge
    from dual;
    
    dbms_output.put_line(l_merge);
    
    -- Back to JSON_OBJECT_T after merging to continue any other logic
    l_merge_json := json_object_t(l_merge);
end;
-- Output
/*
{
    "firstName":"Neil",
    "address":"123 Nevermind Drive",
    "animals":{"dog1":"Fido","dog2":"Fetch"},
    "lastName":"Fernandez"
}
*/

That's it. Hope this can be helpful to anyone dealing with JSON in the Oracle Database that runs into a similar situation.

Here is some more info on JSON_MERGEPATCH:

#JoelKallmanDay