JSON_TRANSFORM Operator CASE

13.5 JSON_TRANSFORM Operator CASE

JSON_TRANSFORM operator CASE conditionally performs a sequence of operations. It is a control operation: it conditionally applies other json_transform operations, which in turn can modify data.

The syntax of a CASE operation is operator CASE followed by one or more WHEN clauses, followed optionally by an ELSE clause, followed by keyword 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.

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.

Note:

A SQL CASE expression differs from a json_transform CASE operation, in these respects:

For SQL CASE, the predicate tested is a SQL comparison. For json_transform CASE, the predicate is a path expression that checks for the existence of some data. (The check is essentially done using json_exists.)
For SQL CASE, 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 CASE, each THEN/ELSE branch holds a (parenthesized) sequence of json_transform operations, which are performed, in order.

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

This example uses operators 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. If no WHEN test succeeds then the ELSE clause is used.

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%.
If neither WHEN test succeeds, the ELSE clause calculates TotalPrice 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(
  data,
  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 ))

No handlers are allowed for operator CASE. (But operators used in the CASE branches can use any handlers they're allowed.)