MySQL JSON_ARRAY() Function

Summary: in this tutorial, you will learn how to use the MySQL JSON_ARRAY() function to create a JSON array of a list of values.

Introduction to the MySQL JSON_ARRAY function

A JSON array is an ordered list of values enclosed in square brackets. For example:

["John", "Doe", 25, "MySQL Developer"]Code language: SQL (Structured Query Language) (sql)

In MySQL, the JSON_ARRAY() function is used to create a JSON array from a list of values.

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

JSON_ARRAY(value1, value2, …)

In this syntax, you specify one or more values separated by commas such as value1, value2, …. These are values you want to include in the JSON array.

The JSON_ARRAY() function returns a JSON array from the input values.

The JSON_ARRAY() function is useful when you need to generate JSON arrays from existing data in your database.

The MySQL JSON_ARRAY() function examples

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

1) Creating a JSON array

The following example uses the JSON_ARRAY() function to create a JSON array:

SELECT 
    JSON_ARRAY('John','Doe', 25, 'MySQL Developer');Code language: SQL (Structured Query Language) (sql)

The query returns the following JSON array:

+-------------------------------------------------+
| JSON_ARRAY('John','Doe', 25, 'MySQL Developer') |
+-------------------------------------------------+
| ["John", "Doe", 25, "MySQL Developer"]          |
+-------------------------------------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

2) Creating a JSON array from table data

We’ll use the employees table from the sample database for the demonstration:

The following example uses the JSON_ARRAY() function to create a JSON array of the first name, email, and job title of employee numbers 1002 and 1056:

SELECT 
  JSON_ARRAY(firstName, email, jobTitle)
FROM 
  employees 
WHERE 
  employeeNumber IN (1002, 1056);Code language: SQL (Structured Query Language) (sql)

Output:

+--------------------------------------------------------+
| JSON_ARRAY(firstName, email, jobTitle)                 |
+--------------------------------------------------------+
| ["Diane", "[email protected]", "President"] |
| ["Mary", "[email protected]", "VP Sales"] |
+--------------------------------------------------------+
2 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

You can use the JSON_PRETTY() function to format the JSON arrays as follows:

SELECT 
  JSON_PRETTY(
    JSON_ARRAY(firstName, email, jobTitle)
  ) 
FROM 
  employees 
WHERE 
  employeeNumber IN (1002, 1056);Code language: SQL (Structured Query Language) (sql)

Output:

+----------------------------------------------------------------+
| JSON_PRETTY(JSON_ARRAY(firstName, email, jobTitle))            |
+----------------------------------------------------------------+
| [
  "Diane",
  "[email protected]",
  "President"
] |
| [
  "Mary",
  "[email protected]",
  "VP Sales"
] |
+----------------------------------------------------------------+
2 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

3) Storing JSON arrays into a table

First, create a table for storing JSON data:

CREATE TABLE contacts(
    employeeNumber INT PRIMARY KEY,
    data JSON
);Code language: SQL (Structured Query Language) (sql)

Second, create JSON arrays using the JSON_ARRAY() function and store them in the contacts table:

INSERT INTO contacts(employeeNumber, data) 
SELECT 
  employeeNumber, 
  JSON_ARRAY(firstName, extension, email) 
FROM 
  employees;Code language: SQL (Structured Query Language) (sql)

In this example, we begin by using the JSON_ARRAY() function to create a JSON array containing the first name, extension, and email of each employee. Subsequently, we use the INSERT INTO SELECT statement to insert the data into the contacts table.

Third, retrieve data from the contacts table:

SELECT * FROM contacts;Code language: SQL (Structured Query Language) (sql)

Here’s the partial output:

+----------------+---------------------------------------------------------+
| employeeNumber | data                                                    |
+----------------+---------------------------------------------------------+
|           1002 | ["Diane", "x5800", "[email protected]"]      |
|           1056 | ["Mary", "x4611", "[email protected]"]     |
|           1076 | ["Jeff", "x9273", "[email protected]"]     |
|           1088 | ["William", "x4871", "[email protected]"] |
...Code language: SQL (Structured Query Language) (sql)

Summary

• Use the JSON_ARRAY() function to create a JSON array from a list of values.