JSON_UPDATE

The JSON_UPDATE function creates a new JSON object from an existing JSON object by changing or deleting the value found using an SQL/JSON path expression.

JSON-expression

An expression that is a built-in string data type. If a character or graphic value is returned, it must contain a correctly formatted JSON object. If a binary value is returned, it must contain the BSON representation of a JSON object.

operation

An expression that is a built-in character or graphic string data type.

A value of SET indicates that the existing value at sql-json-path-expression is to be replaced with new-value.
A value of REMOVE indicates the key specified by sql-json-path-expression and its associated value should be removed.

sql-json-path-expression

A character or graphic string expression that is interpreted as an SQL/JSON path expression which is used to locate the item to be updated or removed. The default JSON path mode is strict. lax mode is not permitted. A new key or array index can only be used for the SET operation when the parent of the key or array index exists in JSON-expression. For information on the content of an SQL/JSON path expression, see sql-json-path-expression.

new-value

A character or graphic string expression that specifies the JSON value to be updated or inserted into the JSON object. If the provided value is not a valid JSON value, it will be changed into a JSON string.

A string containing null will be interpreted as a JSON null value, not a string. A Boolean value, which is represented as a character string of TRUE or FALSE, will be interpreted as a string since a JSON Boolean value must be true or false.

This parameter is ignored when operation is REMOVE.

For a SET operation:

If a value exists at the specified sql-json-path-expression, the value is replaced by the new one.
If a value does not exist at the specified sql-json-path-expression, a new value is added.

For the REMOVE operation:

If the sql-json-path-expression specifies a key, the key and value are removed.
If the sql-json-path-expression specifies an array element, the array element specified by sql-json-path-expression is removed.

The data type of the result is CLOB(2G) CCSID 1208 if JSON-expression is character or graphic data. The data type of the result is BLOB(2G) if JSON-expression is binary data. If a BLOB is returned, it contains the BSON representation of a JSON document.

The result can be null. If the first argument is null, the result is the null value.

Example

These examples work with a JSON object that contains an array of three phone extension values.

CREATE TABLE SANDBOX (DATA VARCHAR(1000) CCSID 1208)

INSERT INTO SANDBOX VALUES ('{"phone":[1111,2222,3333]}')

To add a new lastname entry to the JSON object, provide a path that includes the new key and the new value.
```
UPDATE SANDBOX SET DATA = JSON_UPDATE(DATA, 'SET', '$.lastname', 'HAAS') 
```
The result is the following updated JSON object:
{"phone":[1111,2222,3333],"lastname":"HAAS"}
To update the last name from HAAS to LEE, provide the path to the lastname item.
```
UPDATE SANDBOX SET DATA = JSON_UPDATE(DATA, 'SET', '$.lastname', 'LEE') 
```
The result is the following updated JSON object:
{"phone":[1111,2222,3333],"lastname":"LEE"}
To update the second entry in the array, provide the appropriate subscript into the array.
```
UPDATE SANDBOX SET DATA = JSON_UPDATE(DATA, 'SET', '$.phone[1]', '9999') 
```
The result is the following updated JSON object:
{"phone":[1111,9999,3333],"lastname":"LEE"}
When setting an array entry, if the array index is greater than the array size, the value will be added at the end of the array.
```
UPDATE SANDBOX SET DATA = JSON_UPDATE(DATA, 'SET', '$.phone[7]', '7777') 
```
The result is the following updated JSON object:
{"phone":[1111,9999,3333,7777],"lastname":"LEE"}
To set the first entry of the phones array to null, either of the following can be used:
```
UPDATE SANDBOX SET DATA = JSON_UPDATE(DATA, 'SET', '$.phone[0]', NULL) 
UPDATE SANDBOX SET DATA = JSON_UPDATE(DATA, 'SET', '$.phone[0]', 'null') 
```
The result is the following updated JSON object:
{"phone":[null,9999,3333,7777],"lastname":"LEE"}
To delete the lastname key and value from the JSON object, use the REMOVE option.
```
UPDATE SANDBOX SET DATA = JSON_UPDATE(DATA, 'REMOVE', '$.lastname') 
```
The result is the following updated JSON object:
{"phone":[null,9999,3333,7777]}
To remove the first entry from the phones array, specify the array index.
```
UPDATE SANDBOX SET DATA = JSON_UPDATE(DATA, 'REMOVE', '$.phone[0]') 
```
The result is the following updated JSON object:
{"phone":[null,3333,7777]}