JSON_SET
Descriptionβ
The JSON_SET
function is used to insert or replace data in JSON and return the result.
Syntaxβ
JSON_SET (<json_object>, <path>, <value>[, <path>, <value>, ...])
Parametersβ
<json_object>
: JSON type expression, the target to be modified.<path>
: String type expression, specifies the path where the value is to be inserted<value>
: JSON type or other types supported byTO_JSON
, the value to be inserted.
Return Valueβ
- Nullable(JSON) Returns the modified JSON object
Usage Notesβ
- When the object pointed to by
<path>
exists, its behavior is consistent withJSON_REPLACE
, otherwise its behavior is consistent withJSON_INSERT
Examplesβ
- Path does not exist
select json_set('{}', '$.k', json_parse('{}'), '$.k.k2', 123);
+--------------------------------------------------------+
| json_set('{}', '$.k', json_parse('{}'), '$.k.k2', 123) |
+--------------------------------------------------------+
| {"k":{"k2":123}} |
+--------------------------------------------------------+ - Value pointed to by
<path>
already exists in the JSON objectselect json_set('{"k": 1}', "$.k", 2);
+--------------------------------+
| json_set('{"k": 1}', "$.k", 2) |
+--------------------------------+
| {"k":2} |
+--------------------------------+ - NULL parameters
select json_set(NULL, '$[1]', 123);
+-----------------------------+
| json_set(NULL, '$[1]', 123) |
+-----------------------------+
| NULL |
+-----------------------------+select json_set('{"k": "v"}', NULL, 123);
+-----------------------------------+
| json_set('{"k": "v"}', NULL, 123) |
+-----------------------------------+
| NULL |
+-----------------------------------+select json_set('{"k": "v"}', '$.k[1]', NULL);
+----------------------------------------+
| json_set('{"k": "v"}', '$.k[1]', NULL) |
+----------------------------------------+
| {"k":["v",null]} |
+----------------------------------------+