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 by TO_JSON, the value to be inserted.

Return Value

• Nullable(JSON) Returns the modified JSON object

Usage Notes

1. When the object pointed to by <path> exists, its behavior is consistent with JSON_REPLACE, otherwise its behavior is consistent with JSON_INSERT

Examples

1. Path does not exist
   select json_set('{}', '$.k', json_parse('{}'), '$.k.k2', 123);

   +--------------------------------------------------------+
   | json_set('{}', '$.k', json_parse('{}'), '$.k.k2', 123) |
   +--------------------------------------------------------+
   | {"k":{"k2":123}}                                       |
   +--------------------------------------------------------+

2. Value pointed to by <path> already exists in the JSON object
   select json_set('{"k": 1}', "$.k", 2);

   +--------------------------------+
   | json_set('{"k": 1}', "$.k", 2) |
   +--------------------------------+
   | {"k":2}                        |
   +--------------------------------+

3. 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]}                       |
   +----------------------------------------+