MySQL JSON_OBJECT() Function

In MySQL, the JSON_OBJECT() function returns a JSON object containing all the key-value pairs specified by parameters.

`JSON_OBJECT()` Syntax

Here is the syntax of the MySQL JSON_OBJECT() function:

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

Parameters

key: Required. The Key in the object.
value: Required. The value of key in the object .

Return value

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

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

Maybe there are the following errors during execution:

If the key is NULL, MySQL will return the error: ERROR 3158 (22032): JSON documents may not contain NULL member names..
If there are an odd number of parameters, MySQL will return an error: ERROR 1582 (42000): Incorrect parameter count in the call to native function 'json_object'.

`JSON_OBJECT()` Examples

Here are some examples of JSON_OBJECT().

Basic usage

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

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

Here, the returned JSON object includes two members: name and age, and the value of name is 'Jim', the value of age is 20.

Duplicate keys

If there are duplicate key-value pairs in the parameters, the last key-value pairs are retained in the final returned object.

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

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

Complex objects

A complex JSON object can store more information.

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

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

Here, we created the following JSON object:

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

In the object:

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

JSON_OBJECT() Syntax