Quick Tip: json_mergepatch

Quick Tip for handling the merging of JSON in Oracle Database!

UpdatedMarch 11, 2025

•3 min read•View as Markdown

Neil Fernandez

Comments

Join the discussion

No comments yet. Be the first to comment.

More from this blog

SQLcl Projects - Dynamic Property File Location Definition

Building on the previous topic of leveraging Liquibase properties to modify APEX installations, we will delve into a companion technique. This one enables users to define as many property files as they wish in the artifact to set it when deploying. T...

Jun 4, 20253 min read643

SQLcl Projects - Dynamic Property File Location Definition

SQLcl Projects - Using Liquibase properties to modify your APEX installation

Prereqs Latest SQLcl Existing SQLcl Project with an APEX app Note On average, I prefer to keep the same Schema name, APEX Application ID, Workspace Name, and Workspace ID across environments and have separate databases. I understand that this isn...

Mar 7, 20253 min read1.2K

SQLcl Projects - Using Liquibase properties to modify your APEX installation

SQLcl: Export and Import data with newlines

Situation Recently I was given a seed data file that was generated from SQLcl with a bunch of INSERT statements, and it worked fine in SQL Developer when ran but would fail in SQLcl with the following: SQL> @DEMO_DATA_TABLE.sql Error starting at lin...

Jan 23, 20246 min read416

SQLcl: Export and Import data with newlines

APEX spinner download indicator using Vanilla JavaScript (async/await)

There are plenty of good resources explaining how to create custom download links in APEX so I will skip over that part and list a couple here: - https://oracle-base.com/articles/misc/apex-tips-file-download-from-a-button-or-link - https://joelkallma...

Mar 8, 20233 min read269

APEX spinner download indicator using Vanilla JavaScript (async/await)

Oracle & More

10 posts

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"} } */

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 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.

#JoelKallmanDay**

Command Palette

Comments

More from this blog

There is an easier way: JSON_MERGEPATCH