JSON MERGE Clause

Syntax

json_merge_patch_clause ::= target_expr WITH PATCH json_patch_expr
json_patch_expr ::= map_constructor | array_constructor | constant_expression | variable_reference

Semantics

The JSON MERGE clause conforms to the standard specified in RFC 7396 . This clause allows you to make a set of changes to a JSON document in a single statement. The JSON MERGE clause consists of two expressions: the target expression and the patch expression.

The target expression specifies the JSON document that needs update. When evaluated on a specified table row, this expression must return zero or more valid JSON values. Otherwise, the JSON MERGE clause is a no-op.

The patch expression, when evaluated, must return a single valid JSON value. This document describes the updates to be made to each of the JSON values returned by the target JSON document. If the evaluated patch contains a JSON value that does not exist within the target, the path and its associated value are added. If the target contains the path, its value is replaced with the value in the patch. If the patch contains a path with a null value and the path exists in the target, the field in the path is removed from the target. The updates are made according to the algorithm described in RFC 7396. In summary, if the patch expression returns an empty result, the JSON MERGE clause is a no-op.

The patch expression can be a constant literal, reference to an external variable, map, or an array constructor. The patch expression must not access any table data as the SQL engine evaluates the patch expression before the execution of the UPDATE statement.

You can use the JSON MERGE patch with all other update clauses (SET, ADD, PUT, REMOVE) in a single UPDATE statement.

Note:

  • If the patch expression includes values other than a JSON object, the result replaces the entire target with the entire patch value.
  • If the patch expression includes a JSON object, the entire JSON object is either created (if it does not exist) or replaced in the target.
  • If the patch expression includes a JSON path that does not exist in the target, the path is created.
  • The target can include path expression of either parent-level or nested-level JSON object.

See Example: Updating JSON Data.