How to generate a JSON (JavaScript Object Notation) array using the JSON_ARRAY() function?

JSON_ARRAY(val1, val2, ...) is a MySQL built-in function that converts a list of JSON values into a JSON array. A JSON 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:

SELECT JSON_ARRAY(), JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME());
  -- +--------------+---------------------------------------------+
  -- | JSON_ARRAY() | JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME()) |
  -- +--------------+---------------------------------------------+
  -- | []           | [1, "abc", null, true, "10:55:58.000000"]   |
  -- +--------------+---------------------------------------------+

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

Reference information of the JSON_ARRAY() function:

JSON_ARRAY(val1, val2, ...): json
  Returns a JSON array converted from a given list of values,
  which can be JSON scalars, JSON arrays or JSON objects.

Arguments, return value and availability:
  val1, val2, ...: Zero or more JSON values to be converted.
  json: Return value. The converted JSON array.
  Available since MySQL 5.7.

⇒ JSON_ARRAY_APPEND() - Appending Value to JSON Array

⇐ MySQL Functions on JSON Values

⇑ MySQL Functions on JSON Values

⇑⇑ MySQL Function References