In MySQL, you can use the JSON_OBJECT() function to create a JSON object from a list of key/value pairs. You provide each key/value pair as two separate arguments. Each pair becomes a key/value pair in the resulting JSON object.

You must provide an even number of arguments (otherwise, you’d have an incomplete pair somewhere in your list of arguments).

The function also accepts an empty list (i.e. you provide no arguments). In this case, you’ll get an empty object.

Syntax

The syntax goes like this:

JSON_OBJECT([key, val[, key, val] ...])

The square brackets indicate an optional argument. Therefore, it’s quite valid to use this function without passing in any arguments.

Example 1 – Basic Usage

Here’s an example to demonstrate.

SELECT JSON_OBJECT('City', 'Cairns', 'Population', 139693) AS 'Result';

Result:

+------------------------------------------+
| Result                                   |
+------------------------------------------+
| {"City": "Cairns", "Population": 139693} |
+------------------------------------------+

Example 2 – Empty Strings

Empty strings are valid.

SELECT JSON_OBJECT('City', '', 'Population', 139693) AS 'Result';

Result:

+------------------------------------+
| Result                             |
+------------------------------------+
| {"City": "", "Population": 139693} |
+------------------------------------+

This also applies to the key:

SELECT JSON_OBJECT('', 'Cairns', 'Population', 139693) AS 'Result';

Result:

+--------------------------------------+
| Result                               |
+--------------------------------------+
| {"": "Cairns", "Population": 139693} |
+--------------------------------------+

Example 3 – Empty Lists

Empty lists are also valid. This will result in an empty JSON object.

SELECT JSON_OBJECT() AS 'Result';

Result:

+--------+
| Result |
+--------+
| {}     |
+--------+

Example 4 – NULL Values

The value part of the key/value pair can contain NULL values.

SELECT JSON_OBJECT('City', 'Cairns', 'Population', NULL) AS 'Result';

Result:

+----------------------------------------+
| Result                                 |
+----------------------------------------+
| {"City": "Cairns", "Population": null} |
+----------------------------------------+

But the key part cannot contain NULL values. If a key has a NULL value, an error will occur.

SELECT JSON_OBJECT('City', 'Cairns', NULL, 139693) AS 'Result';

Result:

ERROR 3158 (22032): JSON documents may not contain NULL member names.