How to build a JSON object with 2 field expressions in result set groups using the JSON_OBJECTAGG() function?

JSON_OBJECTAGG(key, value) is a MySQL built-in aggregate function that creates a JSON object with 2 field expressions in result set groups. For example:

SELECT help_category_id, LEFT(JSON_OBJECTAGG(help_topic_id, name), 40)
  FROM mysql.help_topic GROUP BY help_category_id;
  -- +------------------+-----------------------------------------------+
  -- | help_category_id | LEFT(JSON_OBJECTAGG(help_topic_id, name), 40) |
  -- +------------------+-----------------------------------------------+
  -- |                1 | {"0": "HELP_DATE", "1": "HELP_VERSION"}       |
  -- |                2 | {"2": "AUTO_INCREMENT", "6": "BIT", "7":...   |
  -- |                3 | {"3": "HELP COMMAND", "618": "SET", "619...   |
  -- |                4 | {"4": "TRUE", "5": "FALSE"}                   |
  -- |                5 | {"40": "GEOMETRY HIERARCHY", "43": "SPAT...   |
  -- ...
  -- +------------------+-----------------------------------------------+

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_OBJECTAGG() is also a window function, you can call it with the OVER clause to create a JSON object with 2 field expressions in the current window. For example:

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

Reference information of the JSON_OBJECTAGG() function:

JSON_OBJECTAGG(key, value): json
  Takes two column names or expressions as arguments, the first of these
  being used as a key and the second as a value, and returns a JSON
  object containing key-value pairs. Returns NULL if the result contains
  no rows, or in the event of an error.

  If duplicate keys exist in the result set group, only the last value
  encountered is used with that key in the JSON output (“last duplicate
  key wins”).

Arguments, return value and availability:
  key: Required. The field expression used as keys in JSON object.
  value: Required. The field expression used as values in JSON object.
  json: Return value. The JSON object encoded as a string.
  Available since MySQL 5.7.

⇒ MAX() - Maximum Value in Group

⇐ JSON_ARRAYAGG() - Building JSON Array in Group

⇑ MySQL Functions on Aggregation Groups

⇑⇑ MySQL Function References