Purpose
This function inserts data into a JSON document and returns the result.
Syntax
JSON_INSERT(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 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 the current path-value pair is used as the new value for evaluating the next pair.
Path-value pairs for existing paths in the document will be ignored and will not overwrite existing document values. If a path identifies a value of any of the following types, a path-value pair for that path will be added to the document if it does not already exist:
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 with the new value.
Otherwise, path-value pairs for non-existent paths 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_INSERT(@jn, '$.a', 10, '$.c', '[true, false]');
+-----------------------------------------------------+
| JSON_INSERT(@jn, '$.a', 10, '$.c', '[true, false]') |
+-----------------------------------------------------+
| {"a": 1, "b": [2, 3], "c": "[true, false]"} |
+-----------------------------------------------------+
1 row in set