Overview of Inserting, Updating, and Loading JSON Data

10 Overview of Inserting, Updating, and Loading JSON Data

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

Use Standard Database APIs to Insert or Update JSON Data

All of the usual database APIs used to insert or update VARCHAR2 and large-object (LOB) columns can be used for JSON columns. If the JSON column is of data type JSON (recommended) then textual data you input is automatically converted to JSON type.

If you insert or update a JSON column using a client (such as JDBC for Java or Oracle Call Interface for C and C++) that supports JSON type then you can bind client data directly to JSON type instances — no conversion from text to JSON type is needed.

A column of data type JSON is always well-formed JSON data. If you use another data type to store JSON data then 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 an is json 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.

For JSON type data, condition is json is inappropriate, except if you use keywords DISALLOW SCALARS (which disallows JSON documents with top-level scalars). Use of any other is json keywords with JSON type data raises an error.

Inserting a JSON document into a JSON column, or updating data in such a column, is straightforward if the column is of data type JSON, VARCHAR2, CLOB, or BLOB. See Example 4-3 for an example of using SQL to insert.

You can also use a client, such as JDBC for Java or Oracle Call Interface for C or C++, to do this. You can even use an older client, which does not support or recognize JSON data type, to insert JSON data into a JSON type column — the data is implicitly converted for JSON type.

Note:

In addition to the usual ways to insert, update, and load JSON 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 implementations of SODA for several languages, as well as for representational state transfer (REST). See Simple Oracle Document Access (SODA).

Use JSON Transform or JSON Merge Patch To Update a JSON Document

You can use Oracle SQL function json_transform or json_mergepatch to modify specific portions of a JSON document. These functions are not only for updating stored JSON data. You can also use them to modify JSON data on the fly, for further use in a query. The database need not be updated to reflect the modified data.

In addition to providing the input JSON data to each function, you provide the following:

For json_transform, a sequence of modification operations to be performed on parts of the data. Each operation consists of the operation name (e.g. REMOVE) followed by pairs of (1) a SQL/JSON path expression that targets some data to modify and (2) an update operation to be performed on that data. The operations are applied to the input data, in the order specified. Each operation acts on the result of applying the preceding operations.
For json_mergepatch, a JSON Merge Patch document, which is a JSON document that specifies the changes to make to a given JSON document. JSON Merge Patch is an IETF standard.

json_transform provides a superset of what you can do with json_mergepatch.

When json_transform updates a JSON document on disk, the operation is typically performed in place, piecewise, if the data is JSON type; the entire document need not be replaced. Other methods of updating might replace the entire document. With such methods you can specify fine-grained modifications for a JSON document, but when you need to save the changes to disk the entire updated document is written.

Updating with json_transform (regardless of the data type) is also piecewise in another sense: you specify only the document pieces to change, and how. A client need send only the locations of changes (using SQL/JSON path expressions) and the update operations to be performed. This contrasts with sending a complete document to be modified and receiving the complete modified document in return.

On the other hand, json_mergepatch can be easier to use in some contexts where the patch document is generated by comparing two versions of a document. You need not specify or think in terms of specific modification locations and operations — the generated patch takes care of where to make changes, and the changes to be made are implicit. For example, the database can pass part of a JSON document to a client, which changes it in some way and passes back the update patch for the document fragment. The database can then apply the patch to the stored document using json_mergepatch.

Use PL/SQL Object Types To Update a JSON Document

Oracle SQL functions json_transform and json_mergepatch let you modify JSON data in a declarative way. For json_transform, you specify where to make changes and what changes to make, but now in detail how to make them. For json_mergepatch, you specify document-version differences: a patch.

For complex use cases that are not easily handled by these SQL functions you can use PL/SQL code — in particular JSON PL/SQL object-type methods, such as remove() — to modify JSON data procedurally. There are no limitations on the kinds of changes you can make with PL/SQL (it is a Turing-complete programming language). You can parse JSON data into an instance of object-type JSON_ELEMENT_T, make changes to it, serialize it (if textual JSON data is needed), and then store it back in the database.

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