Oracle updating a multiset collection
How to Update JSON with SQL Let's say there's a big corporate re-org, merging the Finance and Accounting departments. So you need to change the name of the Finance department.Pre-19c, to change part of a JSON document, you had to replace the whole thing.
How to Store JSON in Your Database JSON documents are just text.Leading to a big, clunky update like this: update departments_json set department_data = utl_raw.cast_to_raw ( '' ) where department_id = 110; Oracle Database 19c introduces a new option: JSON patching with JSON_mergepatch.This only replaces the relevant section of the document.But I know that you may just want to dump JSON in your database and be done with it. As JSON is text, the obvious choice for storing it is a VARCHAR2 for small documents. But if you're storing bigger documents, we recommend going for BLOB. This is because BLOB stores the document as-is, avoiding character set conversions. The downside of this is you need to do data conversions when you save or retrieve JSON in BLOBs.
Luckily, as we'll see later, this is easy to do.It's better to fetch the parts you want from the table.