JSON_SET

Purpose

This function is used to insert or update data in a JSON document and return the result.

Syntax

JSON_SET(json_doc, path, val[, path, val] ...)

Considerations

The json_doc parameter specifies the JSON document, and path is the path parameter. If any parameter is NULL or the path (if provided) does not locate an object, NULL is returned.

The following situations will result in an error:

• The json_doc parameter is not a valid JSON document.

• The path parameter is not a valid path expression.

• The path parameter contains the * or ** wildcard.

The path-value pairs are evaluated from left to right. The document generated by the path-value pair is used as the new value for the next pair.

Existing path-value pairs in the document will be overwritten by the new value. If the path does not identify an object, the path-value pair will be added to the document according to the following rules:

• If the member does not exist in the existing object, it will be added to the object and associated with the new value.

• If the position is beyond the end of the existing array, the array will be extended with the new value. If the existing value is not an array, it will be automatically wrapped in an array before being extended.

Otherwise, the path-value pair for the non-existent path will be ignored and considered invalid.

Examples

obclient> SET @jn = '{ "a": 1, "b": [2, 3]}';
Query OK, 0 rows affected

obclient> SELECT JSON_SET(@jn, '$.a', 10, '$.c', '[true, false]');
+--------------------------------------------------+
| JSON_SET(@jn, '$.a', 10, '$.c', '[true, false]') |
+--------------------------------------------------+
| {"a": 10, "b": [2, 3], "c": "[true, false]"}     |
+--------------------------------------------------+
1 row in set