Skip to main content

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