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. For better performance, you can load the external-table data into an ordinary table.

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.

From an application-development perspective this makes sense: If a JSON document is used to record application state, the application typically retrieves it, modifies it, and then writes it back to disk, to reflect the updated state. You typically do not want your application to deal with the complexity of tracking piece-wise updates to documents and then writing them back to the database.

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.

Note:

In addition to the usual ways to insert, update, and load data, you can use Simple Oracle Document Access (SODA) APIs. SODA is designed for schemaless application development without knowledge of relational database features or languages such as SQL and PL/SQL. It lets you create and store collections of documents of any kind (not just JSON), retrieve them, and query them, without needing to know how the documents are stored in the database. SODA also provides query features that are specific for JSON documents.

There are two implementations of SODA:

SODA for Java — Java classes that represent database, collection, and document.
SODA for REST — SODA operations as representational state transfer (REST) requests, using any language capable of making HTTP calls.

For information about SODA see Oracle as a Document Store.