How to merge JSON objects in SQL

When working with APEX, handling JSON data structures is a common task, particularly when interacting with REST APIs. There may be instances where you need to merge or layer one JSON object onto another, such as when using default configurations in an application and allowing for customizations. This functionality is typically achieved in JavaScript functions using the options parameter.

You can easily do this in SQL using the json_mergepatch function. The following example highlights how to modify and also add items to a JSON object:

select 
    json_mergepatch(
-- default value
'
{
    "foo": 123,
    "bar": {
        "firstName": "default",
        "lastName": "default"
    }
}
',
-- custom value
-- replace "lastName"
-- adds "middleName"
-- adds "language"
'
{
    "bar": {
        "lastName": "dsouza",
        "middleName": "giffy"
    },
    "language": "english"
}
'
-- If you your resulting JSON will be larger than 4000 then return a clob by uncommented below
-- returning clob
--
-- The "pretty" keyword is optional and just helps with display
pretty
) demo
from sys.dual;

The resulting JSON object is:

{
    "foo": 123,
    "bar": {
        "firstName": "default",
        "lastName": "dsouza",
        "middleName": "giffy"
    },
    "language": "english"
}