Oracle SQL function json_transform modifies a copy of its input JSON data and returns the modified result.

Definitions:

A given json_transform operation applies its operator to the operation's input data, called the operand, which is a subset of the function's copied input data, to modify it.

For example, in this json_transform invocation the input data to the function is {"a":1, "b":2} and there is a single operation, whose operator is REMOVE. The input data to the operation, that is, the operand, is the portion of the (copied) input data that is targeted by path expression $.a. The operation removes field a (that is, member "a":1) from its input data.

json_transform({"a":1, "b":2}, REMOVE '$.a')

Result — modified input data:

{"b":2}

The result of operation is the new, updated state of the (copied) input data, to be used as the starting point for the next operation. Since there is only one operation in this invocation of json_transform, that operation result is also the return value of the function.

Different operators act on their operand in different ways. In the REMOVE operation above, the action is to remove the operand from the input data.

The result of a given json_transform operation is its targeted JSON data, modified. The return value of function json_transform is a copy of its input data, modified by all of its operations — it is always JSON data, of the same SQL type as its input (JSON type or textual, such as VARCHAR2). Function json_transform and its operations transform JSON data to JSON data.

Definitions:

Most json_transform operators are assignment operators, in this sense: they assign a new value to the operand. The value assigned is always a JSON value.

This is the assignment syntax: LHS = RHS, where LHS means left-hand side and RHS means right-hand side.^Foot 1

The operator evaluates the RHS expression and then uses the resulting value (or sequence of values) to act on the operand. Different operators use the LHS and RHS differently.

The LHS is typically a SQL/JSON path expression that targets the current state of json_transform's input data (which initially is copied from its first argument). For operator SET (only), the LHS can alternatively be a SQL/JSON variable, whose value is set to the RHS value — classic assignment.

A SQL/JSON path expression is often used in a query or an existence check (json_exists), to get matching data. But when used in a json_transform LHS, a path expression identifies locations where new values are to be put.

In this sense, an LHS path expression can be said to play the role of a generalized pattern-matching/destructuring variable — it's a complex placeholder, indicating particular parts of the input data to update.

For example, the first SET operation in the following code changes its operand (the second element of input array a) to 5, giving a the value [ 1,5 ]. Then the second SET operation changes its operand (the first element of that updated a value) to 3, giving a the value [ 3,5 ].

json_transform({"a":[ 1,2 ]},
               SET '$.a[1]' = 5,
               SET '$.a[0]' = 3)

Result — modified input data:

{"a":[ 3,5 ]}

The following code is similar, producing the same result, but here the second SET operation calculates the new value for the first a element (a[0]) using the current value of its second element (a[1]). The state of the input data for an operation is always the state that results from the preceding operation.

json_transform({"a":[ 1,2 ]},
               SET '$.a[1]' = 5,
               SET '$.a[0]' = a[1] - 2)

Result — modified input data:

{"a":[ 3,5 ]}

The RHS is an expression of some kind, which is evaluated to produce one or more JSON values that are used in some way to update the LHS data. See JSON_TRANSFORM Right-Hand-Side (RHS) Path Expressions for details.

In the previous examples that have two SET operations, the RHS of each operation is a SQL expression whose value is a SQL number, which is then interpreted as a JSON number.

In the following SET operation the operand is specified by the LHS path expression '$.a'. The value used to update the operand is specified by the RHS SQL expression 'z20' (a literal SQL string), whose value is interpreted as a JSON string.

SET '$.a' = 'z20'

In the following SET operation the RHS SQL expression, a literal string, is wrapped with constructor JSON, to produce a JSON array from that string.

SET '$.a' = JSON('[ 3,1,4 ]')

Result — modified input data:

{"a":[ 3,1,4 ]}

If constructor JSON were not used there then the result would be {"a":"[ 3,1,4 ]"} — the value of field a would be a JSON string, not an array.

In the following code the SET operation's RHS uses a path expression ($a[1]) that targets the second element of array a. The operation updates field a, replacing its array value with the array's second element (2).

json_transform({"a":[ 1,2 ]},
               SET '$.a' = PATH '$.a[1]')

Result — modified input data:

{"a":2}

In the following code the first SET operation sets variable $var to the value of array a's second element (2) multiplied (*) by 3. The second operation then sets the first element of array a to the value of that variable.

json_transform({"a":[ 1,2 ]},
               SET '$var' = PATH '$.a[1] * 3',
               SET '$.a[0]' = $var)

Result — modified input data:

{"a":[ 6,2 ]}