Overview of Inserting, Updating, and Loading JSON Data

9 Overview of Inserting, Updating, and Loading JSON Data

You can use standard database APIs to insert or update JSON data in Oracle Database. You can work directly with JSON data contained in file-system files by creating an external table that exposes it to the database. You can use JSON Merge Patch to update a JSON document.

Use Standard Database APIs to Insert or Update JSON Data

Because JSON data is stored using standard SQL data types, all of the standard database APIs used to insert or update VARCHAR2 and large-object (LOB) columns can be used for columns containing JSON documents. To these APIs, a stored JSON document is nothing more than a string of characters.

You specify that a JSON column must contain only well-formed JSON data by using SQL condition is json as a check constraint. The database handles this check constraint the same as any other check constraint — it enforces rules about the content of the column. Working with a column of type VARCHAR2, BLOB, or CLOB that contains JSON documents is thus no different from working with any other column of that type.

Update operations on a document in a JSON column require the replacement of the entire document. You can make fine-grained modifications to a JSON document, but when you need to save the changes to disk the entire updated document is written.

Inserting a JSON document into a JSON column is straightforward if the column is of data type VARCHAR2 or CLOB — see Example 4-2. The same is true of updating such a column.

But if you use a command-line tool such as SQL*Plus to insert data into a JSON column of type BLOB, or to update such data, then you must convert the JSON data properly to binary format. Example 9-1 is a partial example of this. It assumes that table my_table has a JSON column, json_doc, which uses BLOB storage.

Example 9-1 Inserting JSON Data Into a BLOB Column

The textual JSON data being inserted (shown as partially elided literal data, {...}) contains characters in the database character set, which is WE8MSWIN1252. The data is passed to PL/SQL function UTL_RAW.cast_to_raw, which casts the data type to RAW. That result is then passed to function UTL_RAW.convert, which converts it to character set AL32UTF8.

INSERT INTO my_table (json_doc)
  VALUES (UTL_RAW.convert(UTL_RAW.cast_to_raw('{....}'),
                          'AL32UTF8',
                          'WE8MSWIN1252'));

Use an External Table to Work With JSON Data in File-System Files

External tables make it easy to access JSON documents that are stored as separate files in a file system. Each file can be exposed to Oracle Database as a row in an external table. An external table can also provide access to the content of a dump file produced by a NoSQL database. You can use an external table of JSON documents to, in effect, query the data in file-system files directly. This can be useful if you need only process the data from all of the files in a one-time operation.

But if you instead need to make multiple queries of the documents, and especially if different queries select data from different rows of the external table (different documents), then for better performance consider copying the data from the external table into an ordinary database table, using an INSERT as SELECT statement — see Example 10-4. Once the JSON data has been loaded into a JSON column of an ordinary table, you can index the content, and then you can efficiently query the data in a repetitive, selective way.

Use JSON Merge Patch To Update a JSON Document

You can use Oracle SQL function json_mergepatch or PL/SQL object-type method json_mergepatch() to update specific portions of a JSON document. In both cases you provide a JSON Merge Patch document, which declaratively specifies the changes to make to a a specified JSON document. JSON Merge Patch is an IETF standard.