JSON_UPDATE

The JSON_UPDATE function is used to update existing JSON data in a table.

The schema is SYSTOOLS.

json-value

A BLOB column of a base table. The json-value value must contain a BSON representation of a JSON document.

operation

Indicates what action is to be taken. Possible values are: $set or $unset.

If $set is used, possible outcomes are:

If the search-string is found, the existing value is replaced with the new one.
If the search-string is not found, the field:value pair is added to the document.

If $unset is used, possible outcomes are:

If the search-string is found, it is removed from the document.

search-string

A path-qualified JSON field name that you want to update.

value

The value that you want to set the search-string to.

Examples

The field can specify a portion of a structure, or an element of an array by using the dot notation. The following SQL illustrates how values can be added and removed from a document.

A single record that contains three phone number extensions is added to a table:

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

To add a new field to the record, the JSON_UPDATE function needs to specify the field and value pair:

UPDATE SANDBOX
   SET DATA = 
     JSON_UPDATE(DATA,'{ $set: {"lastname":"HAAS"}}');

If an array index is set that 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}}');

Retrieving the document shows that the lastname field was added to the record:

SELECT BSON2JSON(DATA) FROM SANDBOX;

1
----------------------------------------------
{"phone":[1111,2222,3333,7777],"lastname":"HAAS"}

To remove a field from the document, use the $unset operation:

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

UPDATE SANDBOX
   SET DATA = 
     JSON_UPDATE(DATA,'{ $unset: {"extension":null}}');
SELECT BSON2JSON(DATA) FROM SANDBOX;
1
------------------------------------------------------------
{"phone":[9999,2222,3333,7777]}

An unset array value will be replaced with null.

UPDATE SANDBOX
   SET DATA = 
     JSON_UPDATE(DATA,'{ $unset: {"phone.1":null}}');
SELECT BSON2JSON(DATA) FROM SANDBOX;
1
------------------------------------------------------------
{"phone":[9999,null,3333,7777]}

If you specify a field that is an array type and do not specify an element, you end up replacing the entire field with the value.

UPDATE SANDBOX
   SET DATA = 
     JSON_UPDATE(DATA,'{ $set: {"phone":"9999"}}');

SELECT BSON2JSON(DATA) FROM SANDBOX;
1
----------------------------------
{"phone":"9999","lastname":"HAAS"}

Running the following SQL against the original data works properly.

UPDATE SANDBOX
   SET DATA = 
     JSON_UPDATE(DATA,'{ $set: {"phone.0":9999}}');

SELECT BSON2JSON(DATA) FROM SANDBOX;

1
------------------------------------------------------------
{"phone":[9999,2222,3333]}