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.

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

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

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