How to build a JSON array with a field expression in result set groups using the JSON_ARRAYAGG() function?

JSON_ARRAYAGG(exp) is a MySQL built-in aggregate function that creates a JSON array with a field expression in result set groups. For example:

SELECT help_category_id, LEFT(JSON_ARRAYAGG(name), 40)
  FROM mysql.help_topic GROUP BY help_category_id;
  -- +------------------+---------------------------------------------+
  -- | help_category_id | LEFT(JSON_ARRAYAGG(name), 40)               |
  -- +------------------+---------------------------------------------+
  -- |                1 | ["HELP_DATE", "HELP_VERSION"]               |
  -- |                2 | ["AUTO_INCREMENT", "BIT", "TINYINT", "BO... |
  -- |                3 | ["HELP COMMAND", "SET", "SET CHARACTER S... |
  -- |                4 | ["TRUE", "FALSE"]                           |
  -- |                5 | ["GEOMETRY HIERARCHY", "SPATIAL COLUMNS"... |
  -- ...
  -- +------------------+---------------------------------------------+

SELECT help_category_id, help_topic_id, name
  FROM mysql.help_topic WHERE help_category_id = 5;
  -- +------------------+---------------+--------------------+
  -- | help_category_id | help_topic_id | name               |
  -- +------------------+---------------+--------------------+
  -- |                5 |            40 | GEOMETRY HIERARCHY |
  -- |                5 |            43 | SPATIAL COLUMNS    |
  -- |                5 |            44 | SPATIAL INDEXES    |
  -- +------------------+---------------+--------------------+

JSON_ARRAYAGG() is also a window function, you can call it with the OVER clause to create a JSON array with the given expression in the current window. For example:

SELECT help_topic_id AS tid, help_category_id AS cid, 
  LEFT((JSON_ARRAYAGG(name) OVER w), 40)
  FROM mysql.help_topic 
  WINDOW w AS (PARTITION BY help_category_id);
  -- +-----+-----+------------------------------------------+
  -- | tid | cid | LEFT((JSON_ARRAYAGG(name) OVER w), 40)   |
  -- +-----+-----+------------------------------------------+
  -- |   0 |   1 | ["HELP_DATE", "HELP_VERSION"]            |
  -- |   1 |   1 | ["HELP_DATE", "HELP_VERSION"]            |
  -- |   2 |   2 | ["AUTO_INCREMENT", "BIT", "TINYINT", "BO |
  -- |   6 |   2 | ["AUTO_INCREMENT", "BIT", "TINYINT", "BO |
  -- |   7 |   2 | ["AUTO_INCREMENT", "BIT", "TINYINT", "BO |
  -- |   8 |   2 | ["AUTO_INCREMENT", "BIT", "TINYINT", "BO |
  -- |   9 |   2 | ["AUTO_INCREMENT", "BIT", "TINYINT", "BO |
  -- ...
  -- +-----+-----+------------------------------------------+

Reference information of the JSON_ARRAYAGG() function:

JSON_ARRAYAGG(exp): json
  Creates a JSON array with a field expression in result set groups.

Arguments, return value and availability:
  exp: Required. The field expression used as JSON array member.
  json: Return value. The JSON array encoded as a string.
  Available since MySQL 5.7.

⇒ JSON_OBJECTAGG() - Building JSON Object in Group

⇐ GROUPING() - Identifying Super-Aggregate Row

⇑ MySQL Functions on Aggregation Groups

⇑⇑ MySQL Function References