SET Clause
Syntax
set_clause ::= path_expression "=" expressionSemantics
The SET clause consists of two expressions: the target expression and the new-value expression. The target expression returns the items to be updated. Notice that a target item may be atomic or complex, and it will always be nested inside a complex item (its parent item). For each such target item, the new-value expression is evaluated, and its result replaces the target item within the parent item.
If the target expression returns a NULL item, then either the target item itself is the NULL item, or one of its ancestors is NULL. In the former case, the target item will be replaced by the new item. In the latter case the SET is a no-op.
The new-value expression may return zero or more items. If it returns an empty result, the SET is a no-op. If it returns more than one item, the items are enclosed inside a newly constructed array (this is the same as the way the SELECT clause treats multi-valued expressions in the select list). So, effectively, the result of the new-value expression contains at most one item. This new item is then cast to the type expected by the parent item for the target field. This cast behaves like the cast expression as described in the Cast Expression section. If the cast fails, an error is raised; otherwise, the new item replaces the target item within the parent item.
The new-value expression may reference the implicitly declared variable $, which is bound to the current target item. Use of the $ variable makes it possible to have target expressions that return more than one item. As mentioned already, in this case, the SET clause will iterate over the target items, and for each target item T, bind the $ variable to T, compute the new-value expression, and replace T with the result of the new-value expression.
What if the new-value expression is the (reserved) keyword null? Normally, null is interpreted as the json null value. However, if the parent of the target item is a record, then null will be interpreted as the SQL NULL, and the targeted record field will be set to the SQL NULL.