MariaDB JSON_OBJECT() Function

In MariaDB, JSON_OBJECT() is a built-in function that returns a JSON object containing all key-value pairs specified by parameters.

MariaDB JSON_OBJECT() Syntax

Here is the syntax for the MariaDB JSON_OBJECT() function:

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

Parameters

key

Optional. The key in the object.

value

Optional. The value of key in the object .

Return value

The MariaDB JSON_OBJECT() function evaluates all key-value pairs in the argument and returns a JSON object containing all key-value pairs.

Since all keys in the JSON object are strings, JSON_OBJECT() will convert non-string type key to string type. In order to ensure the stability of the program, we generally use the string type key.

If given an odd number of arguments, MariaDB will return an error: ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_OBJECT'.

MariaDB JSON_OBJECT() Examples

Here are some common examples to show the usages of the Mariadb JSON_OBJECT() function.

Example 1

SELECT JSON_OBJECT('name', 'Jim', 'age', 20);

Output:

+---------------------------------------+
| JSON_OBJECT('name', 'Jim', 'age', 20) |
+---------------------------------------+
| {"name": "Jim", "age": 20}            |
+---------------------------------------+

Here, JSON_OBJECT() returns a JSON object that consists of two members: name and age, where the value of name is 'Jim', and the age value is 20.

Duplicate key

If there are duplicate key-value pairs in the parameters of JSON_OBJECT(), then the duplicate key-value pairs will also be retained in the final returned object.

SELECT JSON_OBJECT('name', 'Jim', 'age', 20, 'name', 'Tim');

Output:

+------------------------------------------------------+
| JSON_OBJECT('name', 'Jim', 'age', 20, 'name', 'Tim') |
+------------------------------------------------------+
| {"name": "Jim", "age": 20, "name": "Tim"}            |
+------------------------------------------------------+

Complex objects

Complex JSON objects can include more information.

SELECT JSON_OBJECT(
        'name',
        'Tim',
        'age',
        20,
        'friend',
        JSON_OBJECT('name', 'Jim', 'age', 20),
        'hobby',
        JSON_ARRAY('games', 'sports')
    ) AS object;

Output:

+------------------------------------------------------------------------------------------------+
| object                                                                                         |
+------------------------------------------------------------------------------------------------+
| {"name": "Tim", "age": 20, "friend": {"name": "Jim", "age": 20}, "hobby": ["games", "sports"]} |
+------------------------------------------------------------------------------------------------+

Here, we create the following JSON object:

{
  "age": 20,
  "name": "Tim",
  "hobby": ["games", "sports"],
  "friend": { "age": 20, "name": "Jim" }
}

where,

• The value of hobby is an array, computed by the JSON_ARRAY() function.
• The value of friend is an object, computed by the JSON_OBJECT() function.

NULL keys

The MariaDB JSON_OBJECT() function treats NULL keys as empty strings:

SELECT JSON_OBJECT(NULL, 1);

Output:

+----------------------+
| JSON_OBJECT(NULL, 1) |
+----------------------+
| {"": 1}              |
+----------------------+

No parameters

If you do not provide any parameter, MariaDB JSON_OBJECT() will return an empty object:

SELECT JSON_OBJECT();

Output:

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

Odd number of arguments

MariaDB will report an error if you pass an odd number of arguments:

SELECT JSON_OBJECT("A");

Output:

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

Conclusion

In MariaDB, JSON_OBJECT() is a built-in function that returns a JSON object containing all key-value pairs specified by parameters.