MariaDB JSON_OBJECT() Explained

In MariaDB, JSON_OBJECT() is a built-in function that returns a JSON object containing the key/value pairs provided as arguments.

Syntax

The syntax goes like this:

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

The function accepts any number of key/value pairs.

The key/value list can also be empty.

Example

Here’s a simple example to demonstrate the function:

SELECT JSON_OBJECT("name", "Homer", "type", "Idiot");

Result:

+-----------------------------------------------+
| JSON_OBJECT("name", "Homer", "type", "Idiot") |
+-----------------------------------------------+
| {"name": "Homer", "type": "Idiot"}            |
+-----------------------------------------------+

No Arguments

As mentionend, the argument list can be empty, in which case, an empty object will be returned:

SELECT JSON_OBJECT();

Result:

+---------------+
| JSON_OBJECT() |
+---------------+
| {}            |
+---------------+

Incorrect Parameter Count

However, if you do provide arguments, they must be an even number of arguments (so that each key has a corresponding value).

Here’s what happens when I pass just one argument to JSON_OBJECT():

SELECT JSON_OBJECT("name");

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_OBJECT'

Same error occurs if I pass three arguments:

SELECT JSON_OBJECT("name", "Homer", "type");

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_OBJECT'

And so on…

Null Key Names

The MariaDB documentation states that an error is returned if any key name is NULL.

However, I get a different result:

SELECT JSON_OBJECT(null, null);

Result:

+-------------------------+
| JSON_OBJECT(null, null) |
+-------------------------+
| {"": null}              |
+-------------------------+