JSON_INSERT

Purpose

JSON_INSERT() inserts data into a JSON document and returns the result.

Syntax

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

Note

json_doc specifies the name of the JSON document, and path specifies the path. If any argument is NULL, the return value is NULL.

An error occurs in the following cases:

• The JSON document specified for json_doc is invalid.

• The path expression specified for path is invalid.

• The specified path contains a * or ** wildcard.

Path-value pairs are evaluated from left to right. The document generated based on a path-value pair is used as the new value to evaluate the next pair.

A path-value pair for an existing path in the document is ignored. It does not overwrite the existing document value. A path-value pair for a nonexisting path in the document adds the value to the document if the path identifies one of the following types of values:

• Member that does not exist in the existing object: The member is added to the object and associated with the new value.

• Beyond the end position of the existing array: The array is extended with the new value. If the existing values are not organized in an array, they are automatically wrapped as an array and then extended with the new value.

Otherwise, the path-value pair of the path that does not exist in the document is 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