Purpose
This function is used to replace existing values in a JSON document and return the result.
Syntax
JSON_REPLACE(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, NULL is returned.
An error will be returned in any of 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 for existing paths in the document will be overwritten with the new values. Path-value pairs for non-existent paths in the document will be ignored and considered invalid.
Examples
obclient> SET @jn = '{ "a": 1, "b": [2, 3, 4]}';
Query OK, 0 rows affected
obclient> SELECT JSON_REPLACE(@jn, '$.a', 10, '$.c', '[true, false]');
+------------------------------------------------------+
| JSON_REPLACE(@jn, '$.a', 10, '$.c', '[true, false]') |
+------------------------------------------------------+
| {"a": 10, "b": [2, 3, 4]} |
+------------------------------------------------------+
1 row in set