MySQL JSON_OBJECT() Function

Summary: in this tutorial, you will learn how to use the JSON_OBJECT() function to create a JSON object from a list of key-value pairs.

Introduction to MySQL JSON_OBJECT() function

The JSON_OBJECT() function allows you to create a JSON object from a list of key-value pairs.

Here’s the syntax of the JSON_OBJECT() function:

JSON_OBJECT([key1, value1, key2, value2, ...])Code language: SQL (Structured Query Language) (sql)

In this syntax:

• key1, key2, …: A list of keys for the JSON object.
• value1, value2, …: A list of corresponding values for the JSON object.

The JSON_OBJECT() function returns a JSON object whose property names and values are the corresponding key/value pairs.

The function raises an error if any key is NULL or the number of arguments that you pass to the function is odd.

MySQL JSON_OBJECT() function examples

Let’s take some examples of using the JSON_OBJECT() function

1) Creating a simple JSON object

The following example uses the JSON_OBJECT() function to create a simple JSON object from a list of key-value pairs:

SELECT 
  JSON_OBJECT(
    'name', 'John', 'age', 30
  ) result;Code language: SQL (Structured Query Language) (sql)

Output:

+-----------------------------+
| result                      |
+-----------------------------+
| {"age": 30, "name": "John"} |
+-----------------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

The result object has two keys "age" and "name" with the corresponding values 30 and "John".

2) Creating a nested JSON object

The following example uses the JSON_OBJECT() function to create a nested JSON object:

SELECT 
  JSON_PRETTY(
    JSON_OBJECT(
      'name', 
      'John', 
      'age', 
      30, 
      'phones', 
      JSON_OBJECT(
        'home', '408-999-9999', 'work', '408-111-2222'
      )
    )
  ) result;Code language: SQL (Structured Query Language) (sql)

Output:

+-------------------------------------------------------------------------------------------------------------+
| result                                                                                                      |
+-------------------------------------------------------------------------------------------------------------+
| {
  "age": 30,
  "name": "John",
  "phones": {
    "home": "408-999-9999",
    "work": "408-111-2222"
  }
} |
+-------------------------------------------------------------------------------------------------------------+Code language: SQL (Structured Query Language) (sql)

3) Combining JSON_OBJECT() with JSON_ARRAY() function

The following example uses the JSON_OBJECT() function with the JSON_ARRAY() function to create an array of JSON objects:

SELECT 
  JSON_ARRAY(
    JSON_OBJECT('name', 'John', 'age', 25), 
    JSON_OBJECT('name', 'Jane', 'age', 28)
  ) result;Code language: SQL (Structured Query Language) (sql)

Output:

+------------------------------------------------------------+
| result                                                     |
+------------------------------------------------------------+
| [{"age": 25, "name": "John"}, {"age": 28, "name": "Jane"}] |
+------------------------------------------------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

4) Creating JSON objects dynamically

We’ll use the employees table from the sample database.

The following example creates JSON objects dynamically using values from the firstName and email columns in the employees table:

SELECT 
  JSON_OBJECT(
    'firstName', firstName, 'email', email
  ) result 
FROM 
  employees;Code language: SQL (Structured Query Language) (sql)

Output:

+----------------------------------------------------------------------+
| result                                                               |
+----------------------------------------------------------------------+
| {"email": "[email protected]", "firstName": "Diane"}      |
| {"email": "[email protected]", "firstName": "Mary"}     |
| {"email": "[email protected]", "firstName": "Jeff"}     |
...Code language: SQL (Structured Query Language) (sql)

5) Error cases

The following example attempts to create a JSON object using the JSON_OBJECT() function but encounters an error due to an odd number of arguments:

SELECT 
  JSON_OBJECT(
    'firstName', firstName, 'email'
  ) result 
FROM 
  employees;Code language: JavaScript (javascript)

Output:

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

Summary

• Use the JSON_OBJECT() function to create a JSON object from a list of key-value pairs.