How to generate a JSON (JavaScript Object Notation) object using the JSON_OBJECT() function?

JSON_OBJECT(key1, val1, key2, val2, ...) is a MySQL built-in function that converts a list of key-value pairs into a JSON object. Key arguments are converted into JSON object keys. Value arguments can be JSON scalars (String, Number, Boolean, or Null), JSON arrays, or JSON objects.

JSON scalars can be specified as literals of MySQL equivalent data types. For example:

SELECT JSON_OBJECT('id', 87, 'name', 'carrot', 'price', 0.99);
  -- +--------------------------------------------------------+
  -- | JSON_OBJECT('id', 87, 'name', 'carrot', 'price', 0.99) |
  -- +--------------------------------------------------------+
  -- | {"id": 87, "name": "carrot", "price": 0.99}            |
  -- +--------------------------------------------------------+

However, JSON arrays and JSON objects need to be constructed with CAST(), JSON_ARRAY(), or JSON_OBJECT() functions. For example:

SELECT JSON_OBJECT('id', 87, 'primes', CAST('[3,5,7]' AS JSON));
  -- +----------------------------------------------------------+
  -- | JSON_OBJECT('id', 87, 'primes', CAST('[3,5,7]' AS JSON)) |
  -- +----------------------------------------------------------+
  -- | {"id": 87, "primes": [3, 5, 7]}                          |
  -- +----------------------------------------------------------+

SELECT JSON_OBJECT('id', 87, 'primes', JSON_ARRAY(3,5,7));
  -- +----------------------------------------------------+
  -- | JSON_OBJECT('id', 87, 'primes', JSON_ARRAY(3,5,7)) |
  -- +----------------------------------------------------+
  -- | {"id": 87, "primes": [3, 5, 7]}                    |
  -- +----------------------------------------------------+

SELECT JSON_OBJECT('id', 87, 'site', JSON_OBJECT('name', 'FYI'));
  -- +-----------------------------------------------------------+
  -- | JSON_OBJECT('id', 87, 'site', JSON_OBJECT('name', 'FYI')) |
  -- +-----------------------------------------------------------+
  -- | {"id": 87, "site": {"name": "FYI"}}                       |
  -- +-----------------------------------------------------------+

Reference information of the JSON_OBJECT() function:

JSON_OBJECT(key1, val1, key2, val2, ...): json
  Returns a JSON object converted from a given list of key-value pairs.
  Key arguments are converted into JSON object keys. Value arguments
  can be JSON scalars (String, Number, Boolean, or Null), JSON arrays,
  or JSON objects.

Arguments, return value and availability:
  key1, val1, key2, val2, ...: One or more JSON key-value pairs
    to be converted.
  json: Return value. The converted JSON object.
  Available since MySQL 5.7.

⇒ JSON_OVERLAPS() - Checking JSON Overlaps

⇐ JSON_MERGE_PRESERVE() - Merging JSON with All Members

⇑ MySQL Functions on JSON Values

⇑⇑ MySQL Function References