How to insert or update child members of a JSON (JavaScript Object Notation) value using the JSON_SET() function?

JSON_SET(json, path1, val1, path2, val2, ...) is a MySQL built-in function that inserts child members of a JSON value at given new locations, and replaces child numbers at given existing locations. The path argument uses the "$", "$.key" or "$[i]" format. The value can be a JSON scalar (String, Number, Boolean, or Null), a JSON array, or a JSON object.

JSON scalars can be specified as literals of MySQL equivalent data types. For example:

SET @j = '{ "a": 1, "b": [2, 3]}';

SELECT JSON_SET(@j, '$.a', 10, '$.c', '[true, false]');
  -- +-------------------------------------------------+
  -- | JSON_SET(@j, '$.a', 10, '$.c', '[true, false]') |
  -- +-------------------------------------------------+
  -- | {"a": 10, "b": [2, 3], "c": "[true, false]"}    |
  -- +-------------------------------------------------+

SELECT JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]');
  -- +----------------------------------------------------+
  -- | JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]') |
  -- +----------------------------------------------------+
  -- | {"a": 1, "b": [2, 3], "c": "[true, false]"}        |
  -- +----------------------------------------------------+

SELECT JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]');
  -- +-----------------------------------------------------+
  -- | JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]') |
  -- +-----------------------------------------------------+
  -- | {"a": 10, "b": [2, 3]}                              |
  -- +-----------------------------------------------------+

SELECT JSON_REPLACE(@j, '$', '[true, false]');
  -- +----------------------------------------+
  -- | JSON_REPLACE(@j, '$', '[true, false]') |
  -- +----------------------------------------+
  -- | "[true, false]"                        |
  -- +----------------------------------------+

However, JSON arrays and JSON objects need to be constructed with CAST(), JSON_ARRAY(), or JSON_OBJECT() functions. For example:

SET @j = '["a", {"b": [1, 2]}, [3, 4]]';

SELECT JSON_SET(@j, '$[2]', CAST('[true, false]' AS JSON));
  -- +-----------------------------------------------------+
  -- | JSON_SET(@j, '$[2]', CAST('[true, false]' AS JSON)) |
  -- +-----------------------------------------------------+
  -- | ["a", {"b": [1, 2]}, [true, false]]                 |
  -- +-----------------------------------------------------+

SELECT JSON_SET(@j, '$[1].b[9]', JSON_ARRAY(true, false));
  -- +----------------------------------------------------+
  -- | JSON_SET(@j, '$[1].b[9]', JSON_ARRAY(true, false)) |
  -- +----------------------------------------------------+
  -- | ["a", {"b": [1, 2, [true, false]]}, [3, 4]]        |
  -- +----------------------------------------------------+

SELECT JSON_SET(@j, '$[1].b[9]', JSON_OBJECT('PI',3.14));
  -- +---------------------------------------------------+
  -- | JSON_SET(@j, '$[1].b[9]', JSON_OBJECT('PI',3.14)) |
  -- +---------------------------------------------------+
  -- | ["a", {"b": [1, 2, {"PI": 3.14}]}, [3, 4]]        |
  -- +---------------------------------------------------+

Reference information of the JSON_SET() function:

JSON_SET(json, path1, val1, path2, val2, ...): modjson
  Replaces child members of a JSON value at given locations, which
  are specified by path arguments in the "$", "$.key" or "$[i]" format.

Arguments, return value and availability:
  json: Required. The JSON value to be updated.
  path1, val1, path2, val2, ...: One or more JSON path-value pairs
    to be processed.
  modjson: Return value. The updated JSON value.
  Available since MySQL 5.7.

Related MySQL functions:

• JSON_INSERT() - Inserting Members to JSON Value
• JSON_REMOVE() - Removing JSON Child Members
• JSON_REPLACE() - Replacing JSON Child Members
• JSON_SET() - Inserting/updating JSON Child Members

⇒ JSON_STORAGE_SIZE() - Storage Size of JSON Value

⇐ JSON_SEARCH() - Searching String in JSON

⇑ MySQL Functions on JSON Values

⇑⇑ MySQL Function References