13 Oracle SQL Function JSON_TRANSFORM

Oracle SQL function json_transform modifies JSON data. It allows multiple modification operations in a single invocation.

With json_transform you specify (1) the modification operations to perform and (2) SQL/JSON path expressions that target the data (places) to modify.

The operations are applied to a copy of the targeted input data, in the order they're specified. Each operation acts on the data that results from the previous operation, which means that it acts on the data resulting from applying all of the preceding operations.Foot 1

Note:

Function json_transform operates on transient data. That is, it operates on a copy of its input data. It never modifies its original input data. It returns that transient data, as updated by all of its argument operations. To update persisted data you need to use json_transform within a SQL UPDATE statement.

If any of the operations in a json_transform invocation fails then an error is raised. The subsequent operations aren't attempted, and no value is returned. In particular, this means that if json_transform is used in an UPDATE statement and it raises an error, then no persistent data is changed. The use of json_transform to update persistent data is atomic.

Note:

When JSON data is stored as JSON data type, updates to it can often be made in-place, that is, piecewise. This partial updating means that only the specific parts of a document that have been modified need to be updated persistently, instead of rewriting an entire document. This is particularly beneficial for small changes in large documents. Partial updating also improves performance by reducing the amount of data that needs to be written to database redo/replication logs.

Before providing details about the various operations and the overall behavior of json_transform, let's look at a few simple examples.

Example 13-1 uses a SQL UPDATE statement with json_transform operator SET to update a single field in one JSON document of a collection. json_transform SET is an "upsert" operator (update or insert). This means that if the path expression in the operation targets an existing field then its value is updated, and if no such field exists then the field is added.

Example 13-1 Updating a Field in One JSON Document Using JSON_TRANSFORM

UPDATE j_purchaseorder po
  SET data = json_transform(data,
                            SET '$.costCenter' = 'Z20')
  WHERE po.data.User = 'ABULL';

The SQL UPDATE statement here uses update operator SET (the first occurrence of "SET" in the example). It updates the purchase-order document in column dataFoot 2 that has "ABULL" as the value of its User field to the data (document) that's the result of the json_transform modification. If column data is JSON-type then only the updated field value is rewritten (in-place update), not the entire document.

The json_transform call returns data that's a copy of the input document, except that its top-level field costCenter has value "Z20". It uses json_transform operator SET to do this (the second occurrence of "SET" in the example). The SQL/JSON path expression $.costCenter targets field costCenter at the top level ($) of the document.

(Note that there are two different kinds/levels of SET operation involved in the example: one for the SQL UPDATE statement, to set the stored document's data to a new value, and the other for the json_transform operation, to set the value of field costCenter in a copy of the input document.)

Example 13-2 is similar, but it sets a single field value in all documents of a collection.

Example 13-2 Setting a Field Value in All Documents Using JSON_TRANSFORM

This example updates all documents in j_purchaseorder.data, setting the value of field lastUpdated to the current timestamp value. 

UPDATE j_purchaseorder
  SET data = json_transform(data,
                            SET '$.lastUpdated' = SYSTIMESTAMP);

If there's no such field in a given input document then json_transform operator SET adds the field. If the field already exists then its value is modified (updated). This "upsert" behavior assumes that the default operation handlers are used: REPLACE ON EXISTING and CREATE ON MISSING.

If we assume the j_purchaseorder data as created in Example 4-3, then there is no such field in any document, so it is added to each document.

Example 13-3 unconditionally adds a field to all documents of a collection. It raises an error if the field to be inserted already exists in some document.

Example 13-3 Adding a Field Using JSON_TRANSFORM

The two uses of json_tranform here are equivalent. They each add field Comments with value "Helpful". The input for the field value is the literal SQL string 'Helpful'. An error is raised if field Comments already exists. (The default behavior for operator SET is CREATE ON MISSING.) 

json_transform(data, INSERT '$.Comments' = 'Helpful')
json_transform(data, SET '$.Comments' = 'Helpful' ERROR ON EXISTING)

Note:

Unlike Oracle SQL function json_mergepatch, which has more limited applicability (it is suitable for updating JSON documents that primarily use objects for their structure, and that do not make use of explicit null values), json_transform is a general modification function.

Function json_transform accepts JSON data as input and returns JSON data as output. You can use any SQL data type that supports JSON data as the input or output: JSON, VARCHAR2, CLOB, or BLOB.Foot 3 By default, the SQL type of the output is the same as that of the input.Foot 4 You can use a RETURNING clause with json_transform to specify a different SQL return type.

Following the sequence of operations you specify, you can include optional PASSING and RETURNING clauses.

  • The PASSING clause specifies SQL bindings of bind variables to SQL/JSON variables. See Use Bind Variables With JSON_TRANSFORM.

    It is the same as for SQL/JSON condition json_exists and the SQL/JSON query functions.

  • The RETURNING clause specifies the return data type.

    It is the same as for SQL/JSON function json_query. (However, the default return type for json_query is different: for JSON type input the json_query default return type is also JSON, but for other input types it is VARCHAR2(4000).Foot 5 )

Besides using json_transform in an UPDATE statement, you can use it in a SELECT list, to create modified copies of the selected documents, which can be returned or processed further. Example 13-4 illustrates this. (It also shows the use of a RETURNING clause.

Example 13-4 Modifying JSON Data On the Fly With JSON_TRANSFORM

This example selects all documents in j_purchaseorder.data, returning pretty-printed, updated copies of them, where field "Special Instructions" has been removed.

It does nothing (no error is raised) if that field is absent from a document: IGNORE ON MISSING is the default behavior.

The return data type is specified as CLOB. (Keyword PRETTY is not available for JSON data type.) 

SELECT json_transform(data,
                      REMOVE '$."Special Instructions"'
                      RETURNING CLOB PRETTY)
  FROM j_purchaseorder;

The last part of an operation specification is an optional set of handlers. Different operators allow different handlers and provide different handler defaults. (An error is raised if you provide a handler for an operator that disallows it.) See JSON_TRANSFORM Operation Handlers.

Most json_transform operations modify data directly. Operations NESTED PATH and CASE can modify data indirectly, by controlling the performance of other operations. In a sense, the same is true of a SET operation, which sets a SQL/JSON variable: the variable value can affect the behavior of operations that directly modify data.

_________________________________________________________

Related Topics

See Also:

JSON_TRANSFORM in Oracle Database SQL Language Reference

Parent topic: Insert, Update, and Load JSON Data


Footnote Legend

Footnote 1: This approach differs, by design, from the snapshot approach used by XQuery Update to update XML data, whereby multiple operations act on exactly the same data, as captured in a static snapshot beforehand.
Footnote 2: Here we assume that j_purchaseorder is a JSON collection table such as created in Example 6-2, and we assume that its data is as created in Example 4-3. Field costCenter exists in all of the documents.
Footnote 3: Data type JSON is available only if database initialization parameter compatible is 20 or greater.
Footnote 4: Do not confuse the SQL return type for function json_transform with the return type of a SQL expression that follows an equal sign (=) in a modification operation (see JSON_TRANSFORM Right-Hand-Side (RHS) Path Expressions).
Footnote 5: You can override this default for json_query using initialization parameter JSON_BEHAVIOR — see SQL/JSON Function JSON_QUERY