JSON_TRANSFORM Right-Hand-Side (RHS) Path Expressions

A json_transform right-hand-side (RHS) path expression is more general that the path expressions allowed elsewhere. Its syntax and behavior are described in detail.

Note:

This topic presents a detailed description of the RHS of json_transform assignment operations when it is a SQL/JSON path expression. Please read topic JSON_TRANSFORM Operations, Including Assignments before reading this topic.

Note:

An assignment RHS (right-hand-side) is one of these:

A SQL expression — its value is used. See Example 13-37.
Keyword PATH followed by a SQL/JSON path expression wrapped with single quotation marks (').^Foot 1 The targeted data is the value that's used.

The result of a json_transform operation is always JSON data. If the RHS is a SQL expression then its value is implicitly converted to a JSON value as in Handling of Input Values For SQL/JSON Generation Functions.

In the simplest case an RHS path expression targets a field. This code adds field b to the input object, {"a":[ 1,2,3 ]}, setting b's value to the sum of the elements field a:

SELECT json_transform('{"a":[ 1,2,3 ]}',
                      SET '$.b' = PATH '$.a[*].sum()');

Result — modified input data:

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

A path-expression RHS for json_transform generally has the syntax and semantics of path expressions used in other SQL functions that act on JSON data. It can include the use of SQL/JSON variables. For example:

SELECT json_transform('{"a":1}',
             SET '$var1' = 2,
             SET '$var2' = PATH '$.a',
             SET '$.b'   = PATH '$var1 + $var2 + $var3'
             PASSING 5 AS "var3");

Result — modified input data:

{"a":1, b:8}

In that code, variable $var1 is set in the first json_transform SET operation, $var2 is set in the second SET operation, and $var3 is set using json_transform's PASSING clause. The third SET operation creates field b, setting its value to the sum of the values of variables $var1, $var2, and $var3.

An assignment RHS can also include calculations, which combine JSON values (represented by path expressions, variables, or literal JSON values) using elementary arithmetic operators: + (addition), - (subtraction), * (multiplication), and / (division). You can nest or otherwise group calculations.

This code adds fields bonus and compensation to the input object.

SELECT json_transform('{"salary":1000, "commission":150}',
                      SET '$.bonus' = PATH '$.salary * $bonusFactor',
                      SET '$.compensation' = PATH '($.salary + $.bonus)
                                                   + $.commission'
                      PASSING 0.05 AS "bonusFactor");

Result — modified input data:

{"salary":1000, "commission":150, "bonus":50, "compensation":1200}

The bonus is calculated as the product of the salary and a bonus factor, whose value is passed as variable $bonusFactor. The compensation value is calculated as the product of the commission with the sum of the salary and the bonus.

Calculations are not allowed in a predicate — this code raises an error:

SET'$.c' = PATH '$.a?( @.x == (@.y + 4) ).b - 2'

The following example uses a nested path uses a nested path to iterate over all of the elements in an array. It calculates the price of each element and adds it to the value of variable $priceVar (initialized to zero). At the end (outside of the nested path), it creates field totalPrice, giving it the value of variable $priceVar.

SELECT json_transform('{"items":[ {"quantity":2, "unitPrice":3},
                                           {"quantity":2, "unitPrice":7} ]}',
                      SET '$priceVar' = PATH '0.00',
                      NESTED PATH '$.items[*]'
                        (SET '$priceVar' =
                             PATH '$priceVar + (@.unitPrice * @.quantity)'),
                      SET '$.totalPrice' = PATH '$priceVar');

Result — modified input data:

{"items":[ {"quantity":2, "unitPrice":3},
           {"quantity":2, "unitPrice":7} ],
 "totalPrice":20}

(The first RHS here could equivalently have been SQL expression (literal) 0.00 instead of PATH '0.00'. In that case, the SQL number 0.00 would be implicitly interpreted as the JSON number 0.00.)

The 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 context then @ is the same as $ in an RHS path expression. See JSON_TRANSFORM Operator NESTED PATH.

The following example uses an array-valued variable, $var, appending its elements, in order, to the input approval arrays for the travel of Jack and Jill. It uses constructor JSON to parse the SQL string '[ 2025, 2026 ]' and return the JSON array [ 2025, 2026 ].

SELECT json_transform('{travel:[ {"name":"Jack", "approval":[ 2023, 2024 ]},
                                 {"name":"Jill", "approval":[ 2024 ]} ]}',
                      SET '$var' = JSON('[ 2025,2026 ]'),
                      APPEND '$.travel.approval' = PATH '$var[*]');

Result — modified input data:


{"travel":[ {"name":"Jack", "approval":[ 2023, 2024, 2025, 2026 ]},
            {"name":"Jill", "approval":[ 2024, 2025, 2026 ]} ]}

(If the path passed to APPEND were just $var and not $var[*], then the result would have appended element [ 2025, 2026 ] to the input approval arrays. Jill's approval value would be [ 2024, [ 2025, 2026 ] ], not what's wanted.)

For most operations an RHS path expression must target a single JSON value. But such a single value could be the result of aggregating multiple values, for example $.a[*].sum().

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 the same as a sequence of that one value.) These operations are APPEND, PREPEND, COPY, MINUS, UNION, and INTERSECT.

For example 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 2

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, suppose 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.

But in the case of PREPEND, if 3 and 4 are prepended together the result is [3,4,1,2], whereas if they're prepended individually, in turn, the result is [4,3,1,2].

This is the behavior of PREPEND: values 3 and 4, in that order, prepended together to input array a ([ 1,2 ]).

SELECT json_transform('{"a":[ 1,2 ], b:[ {c:3}, {c:4} ]}',
                      PREPEND '$.a' = PATH '$.b[*].c')

Result:

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

The same consideration holds for INTERSECT, compared to UNION. If multiple RHS values (with at least two that differ), were handled individually by INTERSECT, instead of together, then the result would always 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 the next value in the sequence would result in an empty intersection.

For example, intersecting [1,2,3,4] with multiple values 3, then 4 individually would first produce [3] (removing all but 3) and then [] (removing all but 4).

Using Conditional Expressions (DECODE and CASE)

Conditional expressions let you set JSON target values by evaluating match/result or condition/result pairs directly within a right-hand-side path expression. Use them when the assigned value depends on JSON content without resorting to separate SET clauses or post-processing.

Both decode() and case() accept SQL/JSON path expressions, literal values, or variables and return JSON nodes. A default result is optional. When no match is found and no default is provided, the functions return null. Results can be of mixed JSON types, so you can assign strings, numbers, dates, or objects in the same expression.

Example 13-42 Using decode() with Match/Result Pairs:

This example matches the second pair and assigns "dog" to field c.

SELECT
          json_transform('{"a":1,"b":2}',           
                    SET '$.c' = PATH 'decode($.b, 3, "cat", 2,
          "dog")')  FROM DUAL;

Result:

json{"a":1,"b":2,"c":"dog"}

If no match is found and no default is supplied, the result is null:

SELECT
          json_transform('{"a":1,"b":2}',           
                    SET '$.c' = PATH 'decode($.a, 3, "cat", 2,
          "dog")')  FROM DUAL;

Result:

json{"a":1,"b":2,"c":null}

You can supply a default value to cover unmatched input:

SELECT
          json_transform('{"a":1,"b":2}',           
                    SET '$.c' = PATH 'decode($.a, 3, "cat", 2,
          "dog", "horse")')  FROM DUAL;

Result:

json{"a":1,"b":2,"c":"horse"}

A decode expression can consist solely of the default, which is returned directly:

SELECT
          json_transform('{"a":1,"b":2}',           
                    SET '$.c' = PATH 'decode($.a,
          "horse")')  FROM DUAL;

Result:

json{"a":1,"b":2,"c":"horse"}

If you omit both match/result pairs and a default, the original expression value is used.

SELECT
          json_transform('{"a":1,"b":2}',           
                    SET '$.c' = PATH 'decode($.a)')  FROM
    DUAL;

Result:

json{"a":1,"b":2,"c":1}

Results can be JSON nodes of different types. This example returns either a number or a date value.

SELECT
          json_transform('{"a":1,"b":2}',           
                    SET '$.c' = PATH 'decode($.b, 3, double(5),
          2, date("2020-01-02"))')  FROM DUAL;

When the value of $.b is 2, the path returns the JSON date node produced by date("2020-01-02"); otherwise it returns the numeric node produced by double(5).

Example 13-43 Using case() with Condition/Result Pairs:

case() evaluates Boolean conditions in order and returns the result paired with the first true condition. Supply an optional default value to handle the case where no condition evaluates to true.

SELECT
          json_transform('{"a":1,"b":2}',           
                    SET '$.c' = PATH 'case($.a > 3, "cat", $.b <
          3, "dog")')  FROM DUAL;

In this code, the second condition matches ($.b < 3), so the value "dog" is assigned.

SELECT
          json_transform('{"a":1,"b":2}',           
                    SET '$.c' = PATH 'case($.a > 0, "cat", $.b <
          3, "dog")')  FROM DUAL;

In the above specified example, the first condition matches ($.a > 0), so the path assigns "cat" even though later conditions could also match.

In the following example, no condition is satisfied and no default is provided, so the result is null.

SELECT
          json_transform('{"a":1,"b":2}',           
                    SET '$.c' = PATH 'case($.a > 1, "cat", $.b <
          2, "dog")')  FROM DUAL;

When no condition is true, the optional default "horse" is used.

SELECT
          json_transform('{"a":1,"b":2}',           
                    SET '$.c' = PATH 'case($.a > 1, "cat", $.b <
          2, "dog", "horse")')  FROM DUAL;

Without any conditions, case() returns the provided default value directly.

SELECT
          json_transform('{"a":1,"b":2}',           
                    SET '$.c' = PATH 'case("horse")')  FROM DUAL;

Note:

case() short-circuits after the first true condition, so later conditions are not evaluated. Use it when the result depends on Boolean expressions instead of discrete matches.

Related Topics

JSON_TRANSFORM Operations, Including Assignments

Footnote Legend

Footnote 1: An error is raised if an RHS includes both a SQL expression and a path expression.
Footnote 2: 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.