13.18 JSON_TRANSFORM Operator SET

JSON_TRANSFORM operator SET (1) sets the value of a SQL/JSON variable, or it (2) replaces or inserts data at a given location.

It sets what the LHS (left-hand-side) of the operation specifies to whatever is specified by the RHS (right-hand-side). The LHS can be either a SQL/JSON variable or a path expression that targets data.

• When the LHS specifies a SQL/JSON variable^Foot 1, the variable is dynamically assigned to whatever is specified by the RHS. (The variable is created if it does not yet exist.) The variable continues to have that value until it is set to a different value by a subsequent SET operation (in the same json_tranform invocation).

  If the RHS is a SQL expression then its value is assigned to the LHS variable. If the RHS is a path expression then its targeted data is assigned to the variable.

  Setting a variable is a control operation; it can affect how subsequent operations modify data, but it does not, itself, directly modify data.

• When the LHS specifies a path expression, the default behavior is like that of SQL UPSERT: replace existing targeted data with the new value, or insert the new value at the targeted location if the path expression matches nothing. (See operator INSERT about inserting an array element past the end of the array.)

Note:

If the right-hand-side (RHS) expression of a json_transform assignment evaluates to a SQL value (or a sequence of values) that is not known to be JSON data, you can convert it to JSON data type by either (1) following the expression with keywords FORMAT JSON or (2) wrapping it with the JSON constructor.^Foot 2 Example 13-37 illustrates this. It converts a literal SQL string, {…}, as input to a JSON object.

CREATE ON MISSING Behavior for SET Operation

The default ON MISSING handler for operation SET is CREATE ON MISSING. This means that if the LHS is a path expression that targets no data, but the parent of that target both (1) exists and (2) is suitable for having a child of the kind provided by the RHS, then that child is created. Only missing child values are created; that is, the test of existence and child-type suitability applies to the parent of the LHS targeted data, not to a grandparent or further ancestors. If the parent doesn't exist or it isn't suitable for the missing child, then the SET operation does nothing.

Parent/child relationship: If the value of a given field is an object, then its fields are its children. If the value of a field is an array, then the array elements are its children.

Some examples where the LHS targets missing data:

SELECT json_transform(JSON('{"alpha" : {"a":1, "b":2}}'),
         SET '$.alpha.z' = 26)

The LHS here specifies a field z that's the child of a field alpha in an object at the top level ($). There's no such field z in the input data. Field alpha exists at the top level, and its value is an object, so it's suitable to contain a child field z. Result:

{"alpha" : {"a":1, "b":2, "z":26}}

A case where the parent (field alpha) doesn't exist:

SELECT json_transform(JSON('{"a":1, "b":2}'),
         SET '$.alpha.z' = 26)

Result: no change to the input data.

{"a":1, "b":2}

A case where the parent (field alpha) exists but it isn't suitable to have field z as a child:

SELECT json_transform(JSON('{"alpha" : [ 1,2 ]}'),
         SET '$.alpha.z' = 26)

The LHS here is equivalent to '$.alpha[*].z'. The parents of targeted field z are the elements of array alpha, but each of those elements is a scalar, not an object — none of them are suitable to contain a field. Result: no change to the input data.

{"alpha" : [ 1,2 ]}

Example 13-33 JSON_TRANSFORM: Using SET To Add a Field To an Object

This code adds member "b":2 to (a copy of) the input object, {"a":1}:

SELECT json_transform('{"a":1}',
                      SET '$.b' = 2);

Result:

{"a":1, "b":2}

Example 13-34 JSON_TRANSFORM: Using SET To Change a Field's Value

This code changes the value of field a from 1 to 2 in (a copy of) the input object, {"a":1}:

SELECT json_transform('{"a":1}',
                      SET '$.a' = 2);

Result:

{"a":2}

Example 13-35 JSON_TRANSFORM: Using SET To Change an Array Element

This code changes the value of the second element (position 1) of array a from 2 to 5 in (a copy of) the input object, {"a":[ 1,2,3 ]}:

SELECT json_transform('{"a":[1,2,3]}',
                      SET '$.a[1]' = 5);

Result:

{"a":[ 1,5,3 ]}

Example 13-36 JSON_TRANSFORM: Using SET To Append a Value To an Array, With null Padding

This code sets the seventh element (position 6) of (a copy of) the input array [1,2,3] to the value 5. Because the input array has only three elements, positions 4, 5, and 6 are filled with null elements.

SELECT json_transform('{"a":[1,2,3]}',
                      SET '$.a[6]' = 5);

Result:

{"a":[ 1,2,3,null,null,null,5 ]}

Example 13-37 JSON_TRANSFORM: Using SET To Create or Replace a Field Value With an Object

This example sets the value of field Address to the JSON object {"street":"8 Timbly Lane", "city":"Penobsky", "state":"Utah"}. It creates the field if it does not exist, and it replaces any existing value for the field.

The input for the field value is a literal SQL string, not a JSON object. The updated field value is a JSON object, however, because the string is wrapped with constructor JSON.

json_transform(data,
               SET '$.Address' = 
                   JSON('{"street":"8 Timbly Rd.",
                          "city":"Penobsky",
                          "state":"UT"}'))

An alternative way to convert the input string to JSON data is to use keywords FORMAT JSON in the SET operation:

json_transform(data,
               SET '$.Address' =
                   '{"street":"8 Timbly Rd.",
                     "city":"Penobsky",
                     "state":"UT"}'
                   FORMAT JSON)

Without using either constructor JSON or FORMAT JSON, the Address field value would be a JSON string that corresponds to the SQL input string. Each of the double-quote (") characters in the input would be escaped in the JSON string:

"{\"street\":\"8 Timbly Rd.\","city\":\"Penobsky\",\"state\":\"UT\"}"

Example 13-38 JSON_TRANSFORM: Using SET To Change an Array Element

This example sets the first element of array Phone to the JSON string "909-555-1212".

json_transform(data,
               SET '$.ShippingInstructions.Phone[0]' = '909-555-1212')

If the value of array Phone before the operation is this:

[ {"type":"Office","number":"909-555-7307"},
  {"type":"Mobile","number":"415-555-1234"} ]

Then this is its value after the modification:

[ "909-555-1212",
  {"type":"Mobile","number":415-555-1234"} ]

These are the handlers allowed for operator SET:

• REPLACE ON EXISTING (default), IGNORE ON EXISTING, ERROR ON EXISTING.

• CREATE ON MISSING (default), IGNORE ON MISSING, ERROR ON MISSING.

• NULL ON NULL (default), IGNORE ON NULL, ERROR ON NULL, REMOVE ON NULL.

• NULL ON EMPTY (default), IGNORE ON EMPTY, ERROR ON EMPTY.

• ERROR ON ERROR (default), IGNORE ON ERROR.

---

Footnote Legend

Footnote 1: A SQL/JSON variable is a dollar sign ($) followed by the variable name (a SQL identifier). See PASSING Clause for SQL Functions and Conditions for the required syntax of a SQL/JSON variable name.
Footnote 2: To use constructor JSON, database initialization parameter compatible must be 20 or greater.