How to insert a new JSON child element to a given JSON (JavaScript Object Notation) value using the JSON_INSERT() function?

JSON_INSERT(json, path1, val1, path2, val2, ...) is a MySQL built-in function that inserts child members in a JSON value at given new locations. Each path-value argument pair specifies locations where new elements to be added and what JSON value to add. 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_INSERT(@j, '$.b[5]', 9);
  -- +------------------------------+
  -- | JSON_INSERT(@j, '$.b[5]', 9) |
  -- +------------------------------+
  -- | {"a": 1, "b": [2, 3, 9]}     |
  -- +------------------------------+

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

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

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

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

Note that if a path argument specifies an existing location (an existing index of a JSON array or an existing key of a JSON object), the associated value will not be inserted. For example:

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

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

Reference information of the JSON_INSERT() function:

JSON_INSERT(json, path1, val1, path2, val2, ...): modjson
  Inserts child members in a JSON value at given new locations.
   which are specified by path arguments using the "$.key" or
  "$[i]" format. If a path argument specifies an existing location
  (an existing index of a JSON array or an existing key of a JSON object),
  the associated value will not be inserted.

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_KEYS() - Exacting Keys from JSON Object

⇐ JSON_STORAGE_FREE() - Free Space in JSON Value

⇑ MySQL Functions on JSON Values

⇑⇑ MySQL Function References