JSON_SET

Purpose

JSON_SET() inserts data into or updates data in a JSON document and returns the results.

Syntax

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

Description

json_doc specifies the name of the JSON document, and path specifies the path. If any argument is NULL or path (if specified) does not identify any object, 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.

The path-value pair of the existing path in the document overwrites the existing document value with the new value. If the object identified by the path does not exist, the path-value pair of the path that does not exist in the document is added to the document according to the following rules:

• 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_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