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 variableFoot 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.

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\"}"

See also json_transform-right-hand-side-rhs-path-expressions.html#GUID-1F575752-DB1F-42B3-9F23-E109E538F3C6__GUID-41EAF5AF-C23F-425D-B223-C00E655A7F31.

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.

Related Topics

Parent topic: Oracle SQL Function JSON_TRANSFORM


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.