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

JSON_ARRAY_APPEND(json, path1, val1, path2, val2, ...) is a MySQL built-in function that appends new JSON values to child arrays of a given JSON value. Each path-value argument pair specifies which JSON child array to be appended and what JSON value to append. 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", ["b", "c"], "d"]';

SELECT JSON_ARRAY_APPEND(@j, '$', 'end');
  -- +-----------------------------------+
  -- | JSON_ARRAY_APPEND(@j, '$', 'end') |
  -- +-----------------------------------+
  -- | ["a", ["b", "c"], "d", "end"]     |
  -- +-----------------------------------+

SELECT JSON_ARRAY_APPEND(@j, '$[1]', 1);
  -- +----------------------------------+
  -- | JSON_ARRAY_APPEND(@j, '$[1]', 1) |
  -- +----------------------------------+
  -- | ["a", ["b", "c", 1], "d"]        |
  -- +----------------------------------+

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

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

SELECT JSON_ARRAY_APPEND(@j, '$', CAST('[1,2,3]' AS JSON));
  -- +-----------------------------------------------------+
  -- | JSON_ARRAY_APPEND(@j, '$', CAST('[1,2,3]' AS JSON)) |
  -- +-----------------------------------------------------+
  -- | ["a", ["b", "c"], "d", [1, 2, 3]]                   |
  -- +-----------------------------------------------------+

SELECT JSON_ARRAY_APPEND(@j, '$', JSON_ARRAY(1,2,3));
  -- +-----------------------------------------------+
  -- | JSON_ARRAY_APPEND(@j, '$', JSON_ARRAY(1,2,3)) |
  -- +-----------------------------------------------+
  -- | ["a", ["b", "c"], "d", [1, 2, 3]]             |
  -- +-----------------------------------------------+

SELECT JSON_ARRAY_APPEND(@j, '$', JSON_OBJECT('End','Yes'));
  -- +------------------------------------------------------+
  -- | JSON_ARRAY_APPEND(@j, '$', JSON_OBJECT('End','Yes')) |
  -- +------------------------------------------------------+
  -- | ["a", ["b", "c"], "d", {"End": "Yes"}]               |
  -- +------------------------------------------------------+

Note that if the path argument points to scalar element, it will be converted into array first and appended with the new value. For example:

SET @j = '["a", ["b", "c"], "d"]';

SELECT JSON_ARRAY_APPEND(@j, '$[1][0]', 3);
  -- +-------------------------------------+
  -- | JSON_ARRAY_APPEND(@j, '$[1][0]', 3) |
  -- +-------------------------------------+
  -- | ["a", [["b", 3], "c"], "d"]         |
  -- +-------------------------------------+

SET @j = '{"a": 1}';

SELECT JSON_ARRAY_APPEND(@j, '$', 'z');
  -- +---------------------------------+
  -- | JSON_ARRAY_APPEND(@j, '$', 'z') |
  -- +---------------------------------+
  -- | [{"a": 1}, "z"]                 |
  -- +---------------------------------+

Reference information of the JSON_ARRAY_APPEND() function:

JSON_ARRAY_APPEND(json, path1, val1, path2, val2, ...): modjson
  Appends new JSON values to child arrays of a given JSON value.
  Each path-value argument pair specifies which JSON child array
  to be appended and what JSON value to append.
  The path argument uses 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.

⇒ JSON_ARRAY_INSERT() - Inserting Value to JSON Array

⇐ JSON_ARRAY() - Creating JSON Array

⇑ MySQL Functions on JSON Values

⇑⇑ MySQL Function References