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.
- 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.
- 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
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.
The result is the following updated JSON object:UPDATE SANDBOX SET DATA = JSON_UPDATE(DATA, 'SET', '$.lastname', 'HAAS')
{"phone":[1111,2222,3333],"lastname":"HAAS"}
- To update the last name from HAAS to LEE, provide the path to the
lastname
item.
The result is the following updated JSON object:UPDATE SANDBOX SET DATA = JSON_UPDATE(DATA, 'SET', '$.lastname', 'LEE')
{"phone":[1111,2222,3333],"lastname":"LEE"}
- To update the second entry in the array, provide the appropriate subscript into the array.
The result is the following updated JSON object:UPDATE SANDBOX SET DATA = JSON_UPDATE(DATA, 'SET', '$.phone[1]', '9999')
{"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.
The result is the following updated JSON object:UPDATE SANDBOX SET DATA = JSON_UPDATE(DATA, 'SET', '$.phone[7]', '7777')
{"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:
The result is the following updated JSON object:UPDATE SANDBOX SET DATA = JSON_UPDATE(DATA, 'SET', '$.phone[0]', NULL) UPDATE SANDBOX SET DATA = JSON_UPDATE(DATA, 'SET', '$.phone[0]', 'null')
{"phone":[null,9999,3333,7777],"lastname":"LEE"}
- To delete the lastname key and value from the JSON object, use the REMOVE
option.
The result is the following updated JSON object:UPDATE SANDBOX SET DATA = JSON_UPDATE(DATA, 'REMOVE', '$.lastname')
{"phone":[null,9999,3333,7777]}
- To remove the first entry from the phones array, specify the array index.
The result is the following updated JSON object:UPDATE SANDBOX SET DATA = JSON_UPDATE(DATA, 'REMOVE', '$.phone[0]')
{"phone":[null,3333,7777]}