13.12 JSON_TRANSFORM Operator NESTED PATH

JSON_TRANSFORM operator NESTED PATH defines a scope — a particular part of your data — within 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 operator NESTED PATH (keyword PATH can be omitted). That path is then 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 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. 

json_transform(data,
               NESTED PATH '$.LineItems[*]'
                 (SET '@Part.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. 

json_transform(data,
               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.

Example 13-21 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 all fields other than UnitPrice and Quantity removed. 

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

Example 13-22 JSON_TRANSFORM: Using NESTED PATH To Aggregate Over Array Elements

This example transforms all documents in j_purchaseorder.data on the fly, to add a new field, OrderTotal, which is the cost of all line-item entries in a purchase order. It does this by first calculating the cost of each line item and then summing those costs. The line-item costs are added to the line-item objects in a new field that is removed after those item costs are summed to produce the order total.

These are the operations performed, in order:

  1. Use operation NESTED PATH with operation SET, to add a new field, LineItemTotal, which is calculated from the unit price, to each element of array LineItems.

  2. Use operation SET to add a new top-level field, OrderTotal, whose value is the sum of all of the LineItemTotal values.

  3. Use operation REMOVE to remove the LineItemTotal fields from the line items.

Note that this is only a SELECT operation; the example does not store the transformed documents in j_purchaseorder.data. For that, you would use an UPDATE statement. 

SELECT json_transform(
         data, 
         NESTED PATH '$.LineItems[*]'
           (SET '@.LineItemTotal' = PATH '@.Part.UnitPrice * @.Quantity'),
         SET '$.OrderTotal' = PATH '$.LineItems[*].LineItemTotal.sum()',
         REMOVE '$.LineItems[*].LineItemTotal')
  FROM j_purchaseorder;

No handlers are allowed for operator NESTED PATH.

Parent topic: Oracle SQL Function JSON_TRANSFORM