How to insert a new JSON value to a given JSON (JavaScript Object Notation) array using the JSON_ARRAY_INSERT() function?

JSON_ARRAY_INSERT(json, path1, val1, path2, val2, ...) is a MySQL built-in function that inserts new JSON values to child arrays of a JSON value before given positions. Each path-value argument pair specifies where to insert and what JSON value to insert. The path argument uses the "$[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", {"b": [1, 2]}, [3, 4]]';

SELECT JSON_ARRAY_INSERT(@j, '$[1]', 'x');
  -- +------------------------------------+
  -- | JSON_ARRAY_INSERT(@j, '$[1]', 'x') |
  -- +------------------------------------+
  -- | ["a", "x", {"b": [1, 2]}, [3, 4]]  |
  -- +------------------------------------+

SELECT JSON_ARRAY_INSERT(@j, '$[1].b[100]', 'x');
  -- +-------------------------------------------+
  -- | JSON_ARRAY_INSERT(@j, '$[1].b[100]', 'x') |
  -- +-------------------------------------------+
  -- | ["a", {"b": [1, 2, "x"]}, [3, 4]]         |
  -- +-------------------------------------------+

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_ARRAY_INSERT(@j, '$[1]', CAST('[7,8,9]' AS JSON));
  -- +--------------------------------------------------------+
  -- | JSON_ARRAY_INSERT(@j, '$[1]', CAST('[7,8,9]' AS JSON)) |
  -- +--------------------------------------------------------+
  -- | ["a", [7, 8, 9], {"b": [1, 2]}, [3, 4]]                |
  -- +--------------------------------------------------------+

SELECT JSON_ARRAY_INSERT(@j, '$[1]', JSON_ARRAY(7,8,9));
  -- +--------------------------------------------------+
  -- | JSON_ARRAY_INSERT(@j, '$[1]', JSON_ARRAY(7,8,9)) |
  -- +--------------------------------------------------+
  -- | ["a", [7, 8, 9], {"b": [1, 2]}, [3, 4]]          |
  -- +--------------------------------------------------+

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

Reference information of the JSON_ARRAY_INSERT() function:

JSON_ARRAY_INSERT(json, path1, val1, path2, val2, ...): modjson
  Inserts new JSON values to child arrays of a JSON value before
  given positions, which are specified by path arguments using the
  "$[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.

⇒ JSON_CONTAINS() - Finding JSON in JSON

⇐ JSON_ARRAY_APPEND() - Appending Value to JSON Array

⇑ MySQL Functions on JSON Values

⇑⇑ MySQL Function References