1. JSON Developer's Guide
  2. Insert, Update, and Load JSON Data
  3. Oracle SQL Function JSON_TRANSFORM

13 Oracle SQL Function JSON_TRANSFORM

Oracle SQL function json_transform modifies JSON documents. You specify operations to perform and SQL/JSON path expressions that target the places to modify. The operations are applied to the input data in the order specified: each operation acts on the data that results from applying all of the preceding operations.

Function json_transform is atomic: if attempting any of the operations raises an error then none of the operations take effect. json_transform either succeeds completely, so that the data is modified as required, or the data remains unchanged. json_transform returns the original data, modified as expressed by the arguments.

You can use json_transform in a SQL UPDATE statement, to update the documents in a JSON column. Example 13-1 illustrates this.

You can use it in a SELECT list, to modify the selected documents. The modified documents can be returned or processed further. Example 13-2 illustrates this.

Function json_transform can accept as input, and return as output, any SQL data type that supports JSON data: JSON, VARCHAR2, CLOB, or BLOB. (Data type JSON is available only if database initialization parameter compatible is 20 or greater.)

The default return (output) data type is the same as the input data type.Foot 1

Unlike Oracle SQL function json_mergepatch, which has 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.

When you specify more than one operation to be performed by a single invocation of json_transform, the operations are performed in sequence, in the order specified. Each operation thus acts on the data that results from applying all of the preceding operations.

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

  • 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).)

  • The PASSING clause specifies SQL bindings of bind variables to SQL/JSON variables. It is the same as for SQL/JSON condition json_exists and the SQL/JSON query functions.

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

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

Here is a brief description of the json_transform operations. See JSON_TRANSFORM Operations and NESTED PATH Operation: Scoping a Sequence of Operations for more complete descriptions.

  • ADD_SET — Add a missing value to an array, as if adding an element to a set.

  • APPEND — Append values to an array.

  • CASE — Conditionally perform json_transform operations.

  • COPY — Replace the elements of an array.

  • INSERT — Insert data at a given location (an object field value or an array position).

  • INTERSECT — Remove array elements other than those in a specified set (set intersection). Remove any duplicates. The operation can accept a sequence of multiple values matched by the right-hand-side (RHS) path expression.

  • KEEP — Remove the data that's not targeted by any of the specified path expressions.

  • MERGE — Merge specified fields into an object (possibly creating the object).

  • MINUS — Remove a set of array elements (set difference). Remove any duplicates. The operation can accept a sequence of multiple values matched by the right-hand-side (RHS) path expression.

  • NESTED PATH — Define a scope (a particular part of your data) in which to apply a sequence of operations.

  • PREPEND — Prepend values to an array.

  • REMOVE — Remove the data that's specified by a path expression.

  • REMOVE_SET — Remove all occurrences of a value from an array, as if removing an element from a set.

  • RENAME — Rename a field.

  • REPLACE — Replace data at a given location.

  • SET — Set a SQL/JSON variable to a specified value, or insert or replace data at a given location.

  • SORT — Sort the elements of an array (change their order).

  • UNION — Add missing array elements from a specified set (set union). Remove any duplicates.

JSON_TRANSFORM Path Expressions: Targeted Data and RHS

Immediately following the keyword for each kind of operation is the path expression for the data targeted by that operation.

Operation KEEP is an exception in that the keyword is followed by one or more path expressions, which target the data to keep — all data not targeted by at least one of these path expressions is removed.

For all modification operations except KEEP, REMOVE, and SORT, the path expression is followed by an equal sign (=) and then a result expression. This is evaluated and the resulting value is used to modify the targeted data. Foot 1

  • For operation RENAME, the result expression must evaluate to a SQL string. Otherwise, an error is raised.

  • For all modification operations except RENAME, the result expression must evaluate to a SQL value that is of JSON data type or that can be rendered as a JSON value. Otherwise, an error is raised because of the inappropriate SQL data type. (This is the same requirement as for the value part of a name–value pair provided to SQL/JSON generation function json_object.)

    For example, if the result expression evaluates to an instance of SQL type VECTOR then that value is rendered as a JSON-language scalar value of type vector.

  • If the result expression evaluates to a SQL value that is not JSON type, you can convert it to JSON data by following the expression immediately with keywords FORMAT JSON. This is particularly useful to convert the SQL string 'true' or 'false' to the corresponding JSON-language value true or false. Example 13-7 illustrates this.

The path expression to the left of the equal sign, which targets the JSON data to update, is sometimes referred to as the left-hand side, or LHS. The LHS is always a SQL/JSON path expression.

The result expression is sometimes referred to as the right-hand side, or RHS of the operation.

The RHS can be eitherFoot 2 a SQL expression or keyword PATH followed by a SQL/JSON path expression wrapped with single quotation marks (').

A json_transform RHS path expression is more general that the path expressions allowed elsewhere. It is an expression that can combine multiple path expressions using elementary arithmetic operations: + (addition), - (subtraction), * (multiplication), and / (division), and you can nest or otherwise group these operations. The only restriction on using arithmetic operations is that you cannot use them within a predicate. For example, this use of a + operation in a predicate raises an error at compile time: 

$.a?(@.x == (@.y + 4)).b - 2

An RHS path expression can also include SQL/JSON variables, which are either assigned in previous json_transform operations or are passed from SQL using a PASSING clause that follows the RHS path expression.

For example, this SET operation updates the value of field compensation to be the sum of these values, where the variable values are passed using a PASSING clause.

  • Field salary multiplied by 0.02, passed to the path expression as SQL/JSON variable $factor

  • field commission

  • 1000, passed as SQL/JSON variable $bonus

SET '$.compensation' = PATH '($.salary * $factor) + $.commission + $bonus'
                       PASSING 1000 AS "bonus", 0.02 AS "factor"

This example is equivalent. It uses operation SET to assign the two variables: 


SET '$bonus' = 1000,
SET '$factor' = 0.02,
SET '$.compensation' = PATH '($.salary * $factor) + $.commission + $bonus'

An RHS can use one or more relative path expressions, where an at-sign character (@) refers to the current node as defined by the innermost enclosing NESTED PATH context.

$ in an RHS refers to the current node of the top-level context. If there is no enclosing NESTED PATH expression then @ is the same as $ in an RHS path expression.

An RHS path expression can specify a single value or a sequence of values. Anything else raises an error. For most operations it must target a single JSON value. (But such a single value could be the result of aggregating multiple values, for example $.a[*].sum().)

However, for operations that expect (require) an LHS that targets an array and that accept an RHS path expression, that path expression can yield a sequence of multiple values. (A single matching value is treated as a sequence of that one value.) These operations are APPEND, PREPEND, COPY, MINUS, UNION, and INTERSECT.

For example, with any of these operations, the RHS path expression $.b[0 to 2] yields, as a sequence, the first through third elements of the array that is the value of field b.

If a path expression in an RHS targets an array, then the entire array is used as the (single) value to be combined with the LHS array.Foot 3

But if the RHS explicitly targets some or all elements of an array, then those elements are used as a sequence of multiple values. These values are combined with the targeted array together, in a single operation. For operations such as APPEND and PREPEND, which add the RHS sequence values to the LHS array, the order of the sequence values is thus preserved in the resulting array.

For example, supposing that array a has value [30,20] and array b has value [2,4,6,8].

  • This operation prepends array b as a single element to a: 

    PREPEND '$.a' = PATH '$.b'

    Array a is set to the value [[2,4,6,8],30,20].

  • This operation prepends the second and fourth elements of array b to array a: 

    PREPEND '$.a' = PATH '$.b[2,4]'

    The multiple values matched by the RHS path expression are prepended to array a together, not individually (a single act prepends them all), so the sequence order is reflected in the resulting array. Array a is set to [4,8,30,20],not [8,4,30,20].

  • This operation prepends all elements of array b, together, to array a: 

    PREPEND '$.a' = PATH '$.b[*]'

    Array a is set to [2,4,6,8,30,20].

Note:

PREPEND and INTERSECT are the only LHS array-targeting operations for which it really matters that multiple values matching an RHS path expression are handled together, as a block, as opposed to being handled, in order, individually.

For example, if RHS path-matching values 3 and 4 are APPENDed, together as a unit, to LHS-targeted array [1,2], the result is the same as if elements 3 and 4 are appended individually, in turn, to the array. The result is in both cases [1,2,3,4]. Adding 3, then 4 is the same as adding 3 and 4 together, keeping them in sequence order.

In the case of INTERSECT, if multiple RHS values (with at least two that differ) were handled individually then the result would be the empty array, []. In effect, after handling the first value in the sequence the resulting intersection would be the singleton array with that value. Handling a different value in the sequence would then result in an empty intersection.

JSON_TRANSFORM Operations

Operation NESTED PATH is described in NESTED PATH Operation: Scoping a Sequence of Operations. This section describes the other json_transform operations:

  • REMOVE — Remove the input data that's targeted by the specified path expression. An error is raised if you try to remove all of the data; that is, you cannot use REMOVE '$'. By default, no error is raised if the targeted data does not exist (IGNORE ON MISSING).

  • KEEP — Remove all parts of the input data that are not targeted by at least one of the specified path expressions. A topmost object or array is not removed; it is emptied, becoming an empty object ({}) or array ([]).

    You can downscope the use of operation KEEP by using it within a NESTED PATH operation. Data outside the scope defined by the nested path is unaffected by the KEEP pruning. Example 13-15 illustrates this.

  • RENAME — Rename the field that's targeted by the specified path expression to the value of the SQL expression that follows the equal sign (=). By default, no error is raised if the targeted field does not exist (IGNORE ON MISSING).

  • SET — Set what the LHS specifies to the value specified by what follows the equal sign (=). The LHS can be either a SQL/JSON variable or a path expression that targets data.

    • When the LHS specifies a SQL/JSON variable, the variable is dynamically assigned to whatever is specified by the RHS. (The variable is created if it does not yet exist.) The variable continues to have that value until it is set to a different value by a subsequent SET operation (in the same json_tranform invocation).

      If the RHS is a SQL expression then its value is assigned to the LHS variable. If the RHS is a path expression then its targeted data is assigned to the variable.

      Setting a variable is a control operation; it can affect how subsequent operations modify data, but it does not, itself, directly modify data.

    • When the LHS specifies a path expression, the default behavior is like that of SQL UPSERT: replace existing targeted data with the new value, or insert the new value at the targeted location if the path expression matches nothing. (See operator INSERT about inserting an array element past the end of the array.)

  • REPLACE — Replace the data that's targeted by the specified path expression with the value of the specified SQL expression that follows the equal sign (=). By default, no error is raised if the targeted data does not exist (IGNORE ON MISSING).

    (REPLACE has the effect of SET with clause IGNORE ON MISSING.)

  • INSERT — Insert the value of the specified SQL expression at the location that's targeted by the specified path expression that follows the equal sign (=), which must be either the field of an object or an array position (otherwise, an error is raised). By default, an error is raised if a targeted object field already exists.

    (INSERT for an object field has the effect of SET with clause CREATE ON MISSING (default for SET), except that the default behavior for ON EXISTING is ERROR, not REPLACE.)

    You can specify an array position past the current end of an array. In that case, the array is lengthened to accommodate insertion of the value at the indicated position, and the intervening positions are filled with JSON null values.

    For example, if the input JSON data is {"a":["b"]} then INSERT '$.a[3]'=42 returns {"a":["b", null, null 42]} as the modified data. The elements at array positions 1 and 2 are null.

  • APPEND — Append the values that are specified by the RHS to the array that's targeted by the LHS path expression. The operation can accept a sequence of multiple values matched by the RHS path expression.

    An error is raised if the LHS path expression targets an existing field whose value is not an array.

    APPEND has the effect of INSERT for an array position of last+1.

    If the RHS targets an array then the LHS array is updated by appending the elements of the RHS array to it, in order. See Example 13-10.

    Tip:

    You can use handler CREATE ON MISSING to create a missing array-valued field targeted by the LHS, filling it from the values specified by the RHS. For example: 

    SELECT json_transform('{"a":[1,2,3]}',
                      APPEND '$.b' = PATH '$.a[0,2]'
                      CREATE ON MISSING)
  FROM DUAL;

    That results in this data:

    { "a" : [1,2,3], "b" : [1,3] }

  • PREPEND — Prepend the values that are specified by the RHS to the array that's targeted by the LHS path expression. The operation can accept a sequence of multiple values matched by the RHS path expression.

    An error is raised if the LHS path expression targets an existing field whose value is not an array.

    When prepending a single value, PREPEND has the effect of INSERT for an array position of 0.

    If the RHS targets an array then the LHS array is updated by prepending the elements of the RHS array to it, in order.

    See also:

    • Note about multiple values matching the RHS path expression of a PREPEND operation being handled together, not individually

    • Example 13-11.

  • COPY — Replace the elements of the array that's targeted by the LHS path expression with the values that are specified by the RHS. An error is raised if the LHS path expression does not target an array. The operation can accept a sequence of multiple values matched by the RHS path expression.

  • ADD_SET — Add the value that's specified by the RHS to the array that's targeted by the LHS path expression, if the value is not already one of its elements. That is, treat the array as if it were a set, so that the value is not added as a duplicate. Note: This is a set operation; the order of all array elements is undefined after the operation.

  • REMOVE_SET — Remove all occurrences of the value that's specified by the RHS from the array that's targeted by the LHS path expression. This treats the array as if it were a set, removing a set element. Note: This is a set operation; the order of all array elements is undefined after the operation.

  • MINUS — Remove all elements of the array that's targeted by the LHS path expression that are equal to a value specified by the RHS. Remove any duplicate elements. Note: This is a set operation; the order of all array elements is undefined after the operation.

  • INTERSECT — Remove all elements of the array that's targeted by the LHS path expression that are not equal to any value specified by the RHS. Remove any duplicate elements. Note: This is a set operation; the order of all array elements is undefined after the operation.

    See also: Note about multiple values matching the RHS path expression of an INTERSECT operation being handled together, not individually.

  • UNION — Add the values specified by the RHS to the array that's targeted by the LHS path expression. Remove any duplicate elements. The operation can accept a sequence of multiple values matched by the RHS path expression.

    Note: This is a set operation; the order of all array elements is undefined after the operation.

  • SORT — Sort the elements of the array targeted by the specified path. The result includes all elements of the array (none are dropped); the only possible change is that they are reordered.

    There are two ways to sort. With either way you can use the keywords REMOVE NULLS, which removes any JSON null values from the values to be sorted.

    • Basic element sort: Sort the array elements by their values, according to the canonical JSON-type sort order.

      The path to the targeted array is optionally followed by keyword ASC (default) or DESC, for ascending or descending sort order, respectively, or by keyword REVERSE, which means reverse the order of the elements.

      The path and optional keyword ASC or DESC is optionally followed by keyword UNIQUE, which means remove any duplicate array elements.

    • Path-directed sort: Sort array elements by the values targeted in an ORDER BY clause, which specifies one or more paths relative to the array.

      Each path is optionally followed by keyword ASC (default) or DESC, for ascending or descending sort order, respectively.

      Sort each pair of elements first by comparing the values targeted by the first ORDER BY path, then by comparing the values targeted by the second ORDER BY path, and so on.

      Each step in a path must be simple: it must target a single JSON value: an array step must target a single array element. A step cannot be a descendant step or include a predicate, a wildcard, or an item method. Otherwise, a compile-time error is raised.

      The simplest example is just ORDER BY followed by the single path @. Being relative to the array, this means order the array elements by comparing each of them with each of the others. In other words, ORDER BY '@' is just another way to specify the basic element sort.

      Each ORDER BY path is checked, in turn, against each pair of array elements.

      • If one element of the pair is matched by the path and the other is not, then the element that is not matched sorts before the element that is matched (after it, with keyword DESC).

      • Otherwise (neither element is matched by the path or both elements are matched by it):

        • If this is the last path to be checked, then the two elements sort according to their values, using the canonical JSON-type sort order (a deep comparison is done). The element with the lower value sorts before the other (after it, with keyword DESC).

        • Otherwise, the order of the two elements is not determined by this path alone — check the same pair using the next ORDER BY path.

      Reasons for an ORDER BY path to not match for a given element include a value of the wrong type and a missing value. These are examples of not matching an array element E:

      • The path targets a string but the targeted value within E is a number (wrong type).

      • The path targets a field of an object, but there is no such object or no such field within E (missing value).

      • The path targets an array element that is out of bounds (missing value).

    The single-path clause ORDER BY '@.name' sorts an array having these elements as follows: 

    [ "cat", "dog", {"animal":cat"}, {"name":"cow"}, {"name":"horse"} ]

    • Elements "cat", "dog", and {"animal":cat"} don't match the path. They sort before the other elements, and they are sorted by their canonical values.

    • Elements {"name":"cow"} and {"name":"horse"} match the path. They are sorted after the other elements, and they are sorted by their values of field name.

    The two-path clause ORDER BY '@.name', '@.age' DESC sorts an array having these elements as follows: 

    [ "cat", "dog", {"animal":cat"},
  {"name":"cow"}, {"name":"cow", "age":2},
  {"name":"horse", "age":6, "color":"black"},
  {"name":"horse", "age":3} ]

    • Elements "cat", "dog", and {"animal":cat"} don't match either path. They sort before the other elements, and they are sorted by their canonical values.

    • Element {"name":"cow"} matches only the first path. It sorts before the elements that match both paths.

    • Elements {"name":"cow", "age":2}, {"name":"horse", "age":6, "color":"black"}, and {"name":"horse", "age":3} match both paths. They sort after the other elements, and they are sorted by their values of, first, field name (ascending), and then, field age (descending).

  • MERGE — Add fields (name and value) matched by the RHS path expression to the object that's targeted by the LHS path expression. Ignore any fields specified by the RHS that are already in the targeted LHS object. If the same field is specified more than once by the RHS then use only the last one in the sequence of matches.

    Tip:

    You can use handler CREATE ON MISSING to create a missing object targeted by the LHS, filling it from the fields specified by the RHS.

  • CASE — Conditionally perform a sequence of json_transform operations.

    This is a control operation; it conditionally applies other operations, which in turn can modify data.

    The syntax is keyword CASE followed by one or more WHEN clauses, followed optionally by an ELSE clause, followed by END.

    • A WHEN clause is keyword WHEN followed by a path expression, followed by a THEN clause.

      The path expression contains a filter condition, which checks for the existence of some data.

    • A THEN or ELSE clause is keyword THEN or ELSE, respectively, followed by parentheses (()) containing zero or more json_transform operations.

      The operations of a THEN clause are performed if the condition of its WHEN clause is satisfied. The operations of the optional ELSE clause are performed if the condition of no WHEN clause is satisfied.

      Tip: You can use a THEN clause with zero operations to conditionally prevent the use of any subsequent clauses.

    • For SQL, the predicate tested is a SQL comparison. For json_transform, the predicate is a path expression that checks for the existence of some data. (The check is essentially done using json_exists.)

    • For SQL, each THEN/ELSE branch holds a SQL expression to evaluate, and its value is returned as the result of the CASE expression. For json_transform, each THEN/ELSE branch holds a (parenthesized) sequence of json_transform operations, which are performed in order.

    The conditional path expressions of the WHEN clauses are tested in order, until one succeeds (those that follow are not tested). The THEN operations for the successful WHEN test are then performed, in order.

    If none of the WHEN tests succeeds then the operations of the optional ELSE clause are performed, in order.

NESTED PATH Operation: Scoping a Sequence of Operations

A nested-path operation defines a scope — a particular part of your data — in which to apply a sequence of operations. The main use case for a nested-path operation is iterating over array elements.

As a construct for downscoping, a nested-path operation limits modification to a subset of your data. It is not, itself, a modification operation.

The operations performed within the scope of a nested-path operation can include other nested-path operations. That is, you can use a nested path within a nested path,..., defining narrower scopes within wider ones, to act on data at any level. In particular, nested paths let you act on the elements of an array nested anywhere.

A nested scope is defined by a target path that immediately follows keywords NESTED PATH (keyword PATH can be omitted). That path is followed by the sequence of zero or more scoped operations, within parentheses ((, )).

The context item for the target path is specified in that path using $, if the NESTED PATH operation with that target is in the topmost (outermost) context. It is specified using @ otherwise, that is, if the NESTED PATH operation with that target is inside another NESTED PATH.

The targeted data specified by the target path becomes the context item for the scoped operations. In those operations, it is denoted @, instead of $.

For example, '$.employees[*]' can be used as the target path in the topmost context; '@.employees[*]' can be used as the target path in a nested scope. The object with targeted field employees is at the top level in the first case; it is at some lower level in the second case.

In either case, the target path defines each element in array employees as the context item for the scoped operations — each operation is applied to one of those elements at a time, in array order.

Similarly, '$.employees[2 to 10]' applies the scoped operations to the third through eleventh employees, in turn; and '$.employees[3,7]' applies them to the fourth and then the seventh employee. (Likewise, with @ in place of $.)

In the following code, the targeted path is $.LineItems[*], so occurrences of @ in the parenthesized sequence of operations are an abbreviation for $.LineItems[*]. This code changes the UnitPrice of each element in array LineItems, by multiplying it by 1.02. 

NESTED PATH '$.LineItems[*]'
  (SET '@.UnitPrice' = PATH '@.UnitPrice * 1.02'

Note that to target each of the elements of an array, instead of the array itself, you must explicitly include [*] after the name of the targeted field whose value is the array — there is no implicit iteration. You can target the array itself (for example '$.employees') if, in a scoped operation, you want to refer to specific array elements, such as the third element, @[2], but this is not a common use case.

You cannot use $ in the LHS of an operation in a nested-path scope; you must use @ instead. This is another way of saying that the transformation/modification for a nested scope is limited to that scope; the operations performed cannot act outside it.

You can, however, use $ in the RHS of a scoped operation. For example, this code first gives each employee a raise of 10% (* factor 1.1), and then assigns each employee the same bonus, which is the value of $.department.bonus. 

NESTED PATH '$.employees[*]'
  (SET '@.salary' = PATH '@.salary * 1.1',
   SET '@.bonus'  = PATH '$.department.bonus')

Occurrences of $ in the RHS of a scoped operation always refer to the topmost (outermost) context of the json_transform invocation.

Within a nested operation (to reiterate):

  • @ refers to the data targeted by the nested path.

  • $ refers to the topmost (outermost) context item, and it can only be used in the RHS of an operation.

JSON_TRANSFORM Operation Handlers

These are the handlers for json_transform operations:

  • ON EMPTY — Specifies what happens if a value targeted by an RHS is JSON null or is missing.

    • NULL ON EMPTY — Return JSON null.

    • ERROR ON EMPTY — Raise an error.

    • IGNORE ON EMPTY — Leave the data unchanged (no modification).

  • ON ERROR — Specifies what happens if trying to resolve an RHS path results in an error.

    • NULL ON ERROR — Return JSON null.

    • ERROR ON ERROR — Raise an error.

    • IGNORE ON ERROR — Leave the data unchanged (no modification).

  • ON EXISTING — Specifies what happens if a path expression matches the data; that is, it targets at least one value. (This handler is irrelevant, and so is ignored, for an LHS that is a SQL/JSON variable.)

    • ERROR ON EXISTING — Raise an error.

    • IGNORE ON EXISTING — Leave the data unchanged (no modification).

    • REPLACE ON EXISTING — Replace data at the targeted location with the value of the SQL result expression.

    • REMOVE ON EXISTING — Remove the targeted data.

  • ON MISMATCH — Specifies what happens if the type of the data targeted by the LHS is unexpected. It applies, in particular, to a MERGE operation, which requires the (LHS) targeted data to be an object, and to operations that require the targeted data to be an array.

    • NULL ON MISMATCH — Return JSON null.

    • ERROR ON MISMATCH — Raise an error.

    • IGNORE ON MISMATCH — Leave the data unchanged (no modification).

    • CREATE ON MISMATCH — Wrap the targeted value in a (singleton) array.

    • REPLACE ON MISMATCH — Replace the targeted value with the empty array ([]).

  • ON MISSING — Specifies what happens if a path expression does not match the data; that is, it does not target at least one value. (This handler is irrelevant, and so is ignored, for an LHS that is a SQL/JSON variable.)

    • ERROR ON MISSING — Raise an error.

    • IGNORE ON MISSING — Leave the data unchanged (no modification).

    • CREATE ON MISSING — Add data at the targeted location.

    Note that for a path-expression array step, an ON MISSING handler does not mean that the targeted array itself is missing from the data — that is instead covered by handler ON EMPTY. An ON MISSING handler covers the case where one or more of the positions specified by the array step does not match the data. For example, array step [2] does not match data array ["a", "b"] because that array has no element at position 2.

  • ON NULL — Specifies what happens if the value of the RHS SQL result expression is NULL. (This handler applies only when the RHS is a SQL expression. If the RHS uses keyword PATH then ON NULL is ignored.)

    • NULL ON NULL — Use a JSON null value for the targeted location.

    • ERROR ON NULL — Raise an error.

    • IGNORE ON NULL — Leave the data unchanged (no modification).

    • REMOVE ON NULL — Remove the targeted data.

    The default behavior for all handlers that allow ON NULL is NULL ON NULL.

  • IGNORE IF ABSENT — Do not raise an error if the targeted data is absent. (By default an error is raised in this case.)

  • IGNORE IF PRESENT — Do not raise an error if the targeted data is already present. (By default an error is raised in this case.)

The handlers allowed for the various operations are as follows.

  • ADD_SET:

    • ERROR ON MISSING (default), IGNORE ON MISSING, CREATE ON MISSING. Create means insert a singleton array at the targeted location. The single array element is the value of the SQL result expression.

    • NULL ON NULL (default), IGNORE ON NULL, ERROR ON NULL.

    • ERROR ON EMPTY (default), IGNORE ON EMPTY, NULL ON EMPTY.

    • IGNORE IF PRESENT.

  • APPEND:

    • ERROR ON MISSING (default), IGNORE ON MISSING, CREATE ON MISSING, NULL ON MISSING. Create means insert a singleton array at the targeted location. The single array element is the value of the SQL result expression.

    • ERROR ON MISMATCH (default), IGNORE ON MISMATCH, REPLACE ON MISMATCH, CREATE ON MISMATCH.

    • NULL ON NULL (default), IGNORE ON NULL, ERROR ON NULL.

    • IGNORE ON EMPTY (default), ERROR ON EMPTY.

  • CASE: no handlers.

  • COPY:

    • CREATE ON MISSING (default), IGNORE ON MISSING, ERROR ON MISSING, NULL ON MISSING.

    • NULL ON NULL (default), IGNORE ON NULL, ERROR ON NULL.

    • IGNORE ON EMPTY (default), ERROR ON EMPTY.

  • INSERT:

    • ERROR ON EXISTING (default), IGNORE ON EXISTING, REPLACE ON EXISTING.

    • CREATE ON MISSING (default).

    • NULL ON NULL (default), IGNORE ON NULL, ERROR ON NULL, REMOVE ON NULL.

    • NULL ON EMPTY (default), IGNORE ON EMPTY, ERROR ON EMPTY.

    • ERROR ON ERROR (default), IGNORE ON ERROR.

  • INTERSECT:

    • ERROR ON MISSING (default), IGNORE ON MISSING, CREATE ON MISSING, NULL ON MISSING.

    • ERROR ON MISMATCH (default).

    • NULL ON NULL (default), IGNORE ON NULL, ERROR ON NULL.

  • KEEP: IGNORE ON MISSING (default), ERROR ON MISSING.

  • MERGE:

    • ERROR ON MISSING (default), IGNORE ON MISSING, CREATE ON MISSING, NULL ON MISSING.

    • ERROR ON MISMATCH (default), IGNORE ON MISMATCH.

    • NULL ON NULL (default), IGNORE ON NULL, ERROR ON NULL.

    • ERROR ON EMPTY (default), IGNORE ON EMPTY.

  • MINUS:

    • ERROR ON MISSING (default), IGNORE ON MISSING, CREATE ON MISSING, NULL ON MISSING.

    • ERROR ON MISMATCH (default).

    • NULL ON NULL (default), IGNORE ON NULL, ERROR ON NULL.

  • NESTED PATH: no handlers.

  • PREPEND: Same as APPEND.

  • REMOVE:

    • REMOVE ON EXISTING (default).

    • IGNORE ON MISSING (default), ERROR ON MISSING.

  • REMOVE_SET:

    • ERROR ON MISSING (default), IGNORE ON MISSING.

    • NULL ON NULL (default), IGNORE ON NULL, ERROR ON NULL.

    • ERROR ON EMPTY (default), IGNORE ON EMPTY, NULL ON EMPTY.

    • IGNORE IF ABSENT.

  • RENAME:

    • REPLACE ON EXISTING (default).

    • IGNORE ON MISSING (default), ERROR ON MISSING.

  • REPLACE:

    • REPLACE ON EXISTING (default).

    • IGNORE ON MISSING (default), ERROR ON MISSING, CREATE ON MISSING.

    • NULL ON NULL (default), IGNORE ON NULL, ERROR ON NULL, REMOVE ON NULL.

    • NULL ON EMPTY (default), IGNORE ON EMPTY, ERROR ON EMPTY.

    • ERROR ON ERROR (default), IGNORE ON ERROR.

  • SET:

    • REPLACE ON EXISTING (default), IGNORE ON EXISTING, ERROR ON EXISTING.

    • CREATE ON MISSING (default), IGNORE ON MISSING, ERROR ON MISSING.

    • NULL ON NULL (default), IGNORE ON NULL, ERROR ON NULL, REMOVE ON NULL.

    • NULL ON EMPTY (default), IGNORE ON EMPTY, ERROR ON EMPTY.

    • ERROR ON ERROR (default), IGNORE ON ERROR.

  • SORT:

    • IGNORE ON MISSING (default), ERROR ON MISSING, NULL ON MISSING.

    • ERROR ON MISMATCH (default), IGNORE ON MISMATCH, NULL ON MISMATCH.

    • ERROR ON EMPTY (default), IGNORE ON EMPTY.

  • UNION:

    • ERROR ON MISSING (default), IGNORE ON MISSING, CREATE ON MISSING, NULL ON MISSING.

    • ERROR ON MISMATCH (default).

    • NULL ON NULL (default), IGNORE ON NULL, ERROR ON NULL.

  • WHEN: no handlers

Example 13-1 Updating a JSON Column Using JSON_TRANSFORM

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

If the field already exists then its value is replaced; otherwise, the field and its value are added. (That is, the default handlers are used: REPLACE ON EXISTING and CREATE ON MISSING.) 

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

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

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

It does nothing (no error is raised) if the field does not exist: IGNORE ON MISSING is the default behavior.

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

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

Example 13-3 Adding a Field Using JSON_TRANSFORM

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

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

Example 13-4 Removing a Field Using JSON_TRANSFORM

This example removes occurrences of field LineItems where the value of field LineItems.Part.UPCCode is 85391628927. It does nothing (no error is raised) if the field does not exist: IGNORE ON MISSING is the default behavior. 

json_transform(po_document,
               REMOVE '$.LineItems?(@.Part.UPCCode == $v1)')
               PASSING 85391628927 AS "v1")

The UPC code used for filtering is provided as the value of SQL/JSON variable $v1, which corresponds to SQL bind variable v1 from the PASSING clause. This technique avoids query recompilation, which can be important for queries that you use often.

Example 13-5 Creating or Replacing a Field Value Using JSON_TRANSFORM

This example sets the value of field Address to the JSON object {"street":"8 Timbly Lane", "city":"Penobsky", "state":"Utah"}. It creates the field if it does not exist, and it replaces any existing value for the field. The input for the field value is a literal SQL string. The updated field value is a JSON object, because FORMAT JSON is specified for the input value. 

json_transform(po_document,
               SET '$.Address' =
                   '{"street":"8 Timbly Rd.",
                     "city":"Penobsky",
                     "state":"UT"}'
                   FORMAT JSON)

If database initialization parameter compatible is 20 or greater than an alternative to using keywords FORMAT JSON is to apply JSON data type constructor JSON to the input data for the field value. 

json_transform(po_document,
               SET '$.Address' = 
                   JSON('{"street":"8 Timbly Rd.",
                          "city":"Penobsky",
                          "state":"UT"}'))

Without using either FORMAT JSON or constructor JSON, the Address field value would be a JSON string that corresponds to the SQL input string. Each of the double-quote (") characters in the input would be escaped in the JSON string: 

"{\"street\":\"8 Timbly Rd.\","city\":\"Penobsky\",\"state\":\"UT\"}"

Example 13-6 Replacing an Existing Field Value Using JSON_TRANSFORM

This example sets the value of field Address to the JSON object {"street":"8 Timbly Lane", "city":"Penobsky", "state":"Utah"}. It replaces an existing value for the field, and it does nothing if the field does not exist. The only difference between this example and Example 13-5 is the presence of handler IGNORE ON MISSING. 

json_transform(po_document,
               SET '$.Address' =
                   '{"street":"8 Timbly Rd.",
                     "city":"Penobsky",
                     "state":"UT"}'
                   FORMAT JSON
                   IGNORE ON MISSING)

Example 13-7 Using FORMAT JSON To Set a JSON Boolean Value

This example sets the value of field AllowPartialShipment to the JSON-language Boolean value true. Without keywords FORMAT JSON it would instead set the field to the JSON-language string "true". 

json_transform(po_document,
               SET '$.AllowPartialShipment' = 'true' FORMAT JSON)

Example 13-8 Setting an Array Element Using JSON_TRANSFORM

This example sets the first element of array Phone to the JSON string "909-555-1212". 

json_transform(po_document,
               SET '$.ShippingInstructions.Phone[0]' = '909-555-1212')

If the value of array Phone before the operation is this: 

[ {"type":"Office","number":"909-555-7307"},
  {"type":"Mobile","number":"415-555-1234"} ]

Then this is the value after the modification:

[ "909-555-1212",
  {"type":"Mobile","number":415-555-1234"} ]

Example 13-9 Appending an Element To an Array Using JSON_TRANSFORM

These two uses of json_tranform are equivalent. They each append element "909-555-1212" to array Phone. 

json_transform(po_document,
               APPEND '$.ShippingInstructions.Phone' =
                      '909-555-1212')
json_transform(po_document,
               INSERT '$.ShippingInstructions.Phone[last+1]' =
                      '909-555-1212')

Example 13-10 Appending Multiple Elements To an Array Using JSON_TRANSFORM

This example appends phone numbers "415-555-1234" and "909-555-1212", in that order, to each Phone field whose value is an array. Nonarray Phone fields are ignored (not matched). So each resulting Phone field ends with [ ..., "415-555-1234", "909-555-1212" ].

The elements to append, and the order in which to append them, are provided here by an array, which in this case is the literal JSON value [ "415-555-1234", "909-555-1212" ], constructed in SQL using the JSON constructor.

This array is passed to the APPEND operation as the value of SQL/JSON variable $new. The elements to be appended are all of the array elements, in order; they are specified in the RHS path expression using [*]. 

json_transform(po_document,
               SET '$new' = JSON('[ "415-555-1234", "909-555-1212" ]'),
               APPEND '$.ShippingInstructions.Phone' =
                       PATH '$new[*]')

As an alternative to using the JSON constructor, you can use FORMAT JSON: 

json_transform(po_document,
               SET '$new' = '[ "415-555-1234", "909-555-1212" ]' FORMAT JSON,
               APPEND '$.ShippingInstructions.Phone' =
                       PATH '$new[*]')

Example 13-11 Prepending Multiple Elements To an Array Using JSON_TRANSFORM

This example is similar to Example 13-10. It prepends phone numbers "415-555-1234" and "909-555-1212" to each Phone field whose value is an array. So each resulting Phone field starts with ["415-555-1234", "909-555-1212",...]. 

json_transform(po_document,
               SET '$new' = JSON('[ "415-555-1234", "909-555-1212" ]'),
               PREPEND '$.ShippingInstructions.Phone' =
                       PATH '$new[*]')

Example 13-12 Removing Array Elements That Satisfy a Predicate Using JSON_TRANSFORM

This example removes all objects in the LineItems array whose UPCCode is 85391628927. These are the array elements that satisfy the specified predicate, which requires an object with field Part whose value is an object with field UPCCode of value 85391628927. 

json_transform(po_document,
               REMOVE '$.LineItems[*]?(@.Part.UPCCode == 85391628927)')

Example 13-13 Sorting Elements In an Array By Field Values Using JSON_TRANSFORM

This example sorts the objects in array LineItems first by field Part.UnitPrice and then by field ItemNumber, in both cases from higher to lower number (keyword DESC). (Ascending order, ASC, is the default.) 

SELECT json_transform(po_document,
                      SORT '$.LineItems'
                        ORDER BY '$.Part.UnitPrice' DESC,
                                 '$.ItemNumber' DESC
                      RETURNING VARCHAR2(4000))
  FROM j_purchaseorder;

Here is one row of the result. The elements are sorted by descending UnitPrice value. Those elements that have the same UnitPrice value are sorted by descending ItemNumber value. 

{"LineItems" :
 [ {"ItemNumber" : 1,
    "Part" :
    {"Description" : "Making the Grade",
     "UnitPrice"   : 20,
     "UPCCode"     : 27616867759},
    "Quantity" : 8},
   {"ItemNumber" : 3,
    "Part" :
    {"Description" : "Eric Clapton: Best Of 1981-1999",
     "UnitPrice"   : 19.95,
     "UPCCode"     : 75993851120},
    "Quantity" : 5},
   {"ItemNumber" : 2,
    "Part" :
    {"Description" : "Nixon",
     "UnitPrice"   : 19.95,
     "UPCCode"     : 717951002396},
    "Quantity" : 5} ]}

Example 13-14 Updating Each Element of an Array Using JSON_TRANSFORM

This example uses operation NESTED PATH to increase the unit price of each line item by a factor of 1.2, and to add a new field, TotalPrice, calculated from the updated unit price, to each array element (object). 

json_transform(po_document,
               NESTED PATH '$.LineItems[*]'
                 (SET '@.TotalPrice' = PATH '@.Quantity * @.Part.UnitPrice'))

Example 13-15 Downscoping with NESTED PATH, To Limit JSON_TRANSFORM Pruning by KEEP

This example limits the scope of a KEEP operation to a specific nested path. Data outside that scope is not pruned. The result is that only elements of array LineItems have fields other than UnitPrice and Quantity removed. 

json_transform(po_document,
               NESTED PATH '$.LineItems[*]'
                 (KEEP '@.Part.UnitPrice', '@.Quantity')))

Example 13-16 JSON_TRANSFORM: Controlling Modifications with SET and CASE

This example uses CASE and SET to set field TotalPrice conditionally, creating it if it doesn't exist. When applied to the data each WHEN test is tried in turn, until one succeeds. The SET operation corresponding to that successful test is then performed. the second applies when Quantity is at least 5 but smaller than 7. When no WHEN clause applies (Quantity is not smaller than 7)

  • The first WHEN clause applies to data with field Quantity smaller than 5. Field TotalPrice is calculated with no discount.

  • The second WHEN clause applies to data with field Quantity at least 5 but smaller than 7. Field TotalPrice is calculated with a discount of 10%.

  • When neither WHEN test succeeds (ELSE clause), TotalPrice is calculated with a discount of 15%.

    Note that this clause applies also when field Quantity does not exist or is a non-numeric JSON value that does not compare less than 7. 

json_transform(
  po_document,
  NESTED PATH '$.LineItems[*]'
    ( CASE WHEN '@?(@.Quantity < 5)' THEN
             ( -- No discount
               SET '@.TotalPrice' = PATH '@.Quantity * @.Part.UnitPrice' )
           WHEN '@?(@.Quantity < 7)' THEN
             ( -- 10% discount
               SET '@.TotalPrice' = PATH '@.Quantity * @.Part.UnitPrice * 0.9' )
           ELSE
             ( -- 15% discount
               SET '@.TotalPrice' = PATH '@.Quantity * @.Part.UnitPrice * 0.85' )
      END ))

Related Topics

See Also:

Parent topic: Insert, Update, and Load JSON Data


Footnote Legend

Footnote 1: Do not confuse the SQL return type for function json_transform with the type of the SQL result expression that follows an equal sign (=) in a modification operation.
Footnote 2: An error is raised if you include both a SQL expression and a path expression.
Footnote 3: This is, in effect, just a case of handling a single RHS path-matching value, in this case an array, as if it were a singleton sequence of that value.