Menu

MariaDB JSON_OBJECT() Function: Syntax and Examples

MariaDB JSON_OBJECT() accepts zero or more key-value pairs and returns a JSON object. An empty argument list returns {}. If the list has an odd number of arguments, or any key is NULL, MariaDB raises an error. See the official MariaDB JSON_OBJECT reference.

MariaDB JSON_OBJECT() Syntax

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

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

Parameters

Each object member uses one key argument followed by its value. The list can be empty, but a nonempty list must contain complete key-value pairs. A NULL key is invalid.

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.

MariaDB JSON_OBJECT() Examples

Here are examples 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 keys

MariaDB can preserve repeated key names in the returned JSON text. When an object has duplicate keys, MariaDB documents that functions such as JSON_EXTRACT() can access only the first key-value pair. Avoid duplicate keys when later code needs an unambiguous value. See MariaDB’s JSON type documentation.

SELECT
    JSON_OBJECT('name', 'Jim', 'name', 'Tim') AS object,
    JSON_EXTRACT(JSON_OBJECT('name', 'Jim', 'name', 'Tim'), '$.name') AS extracted_name;

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 are invalid

MariaDB reports an error when a key is NULL; it does not convert the key to an empty string:

SELECT JSON_OBJECT(NULL, 1);

Consult the server’s error output for the exact diagnostic.

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

Every key must be followed by a value. An odd number of arguments raises an error:

SELECT JSON_OBJECT('A');

MariaDB reports an incorrect-parameter-count error.