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] ...)
Description
The json_doc parameter specifies the JSON document, and path is a path parameter. If either parameter is NULL or the object is not located at the specified path (if given), NULL is returned.
An error will be returned in the following scenarios:
The
json_docparameter is not a valid JSON document.The
pathparameter is not a valid path expression.The
pathparameter contains the * or ** wildcard.
Path-value pairs are evaluated from left to right. The document generated by a path-value pair is used as the new value for evaluating the next pair.
The path-value pairs of existing paths in the document will be overwritten with the new values. If an object identified by a path does not exist, path-value pairs for that path will be added to the document according to the following rules:
A member that does not exist in the current object. This member is added to the object and associated with a new value.
Beyond the end of the existing array. In this case, the array is extended with the new value. If the existing value is not an array, it is automatically wrapped in an array before being extended.
Otherwise, path-value pairs for paths that do not exist in the document will be ignored and 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