JSON_SET

Description

This function inserts or updates data in a JSON document and returns the result.

Syntax

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

Description

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, the function returns NULL.

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.

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

Existing path-value pairs in the document will be overwritten with new values. If the path does not identify an existing object, the path-value pair will be added to the document based on 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 as an array before being extended.

Otherwise, path-value pairs for non-existent paths in the document will be ignored and considered invalid.

Examples

SET @jn = '{ "a": 1, "b": [2, 3]}';
Query OK, 0 rows affected (0.000 sec)

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 (0.001 sec)