{"id":11964,"date":"2023-10-30T06:36:36","date_gmt":"2023-10-30T13:36:36","guid":{"rendered":"https:\/\/www.mysqltutorial.org\/?page_id=11964"},"modified":"2023-11-23T05:44:27","modified_gmt":"2023-11-23T12:44:27","slug":"mysql-json_array","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-json\/mysql-json_array\/","title":{"rendered":"MySQL JSON_ARRAY() Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL JSON_ARRAY()<\/code> function to create a JSON array of a list of values.<\/p>\n\n\n\n

Introduction to the MySQL JSON_ARRAY function<\/h2>\n\n\n\n

A JSON array is an ordered list of values enclosed in square brackets. For example:<\/p>\n\n\n

[\"John\", \"Doe\", 25, \"MySQL Developer\"]<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In MySQL, the JSON_ARRAY()<\/code> function is used to create a JSON array from a list of values.<\/p>\n\n\n\n
Here’s the syntax of the JSON_ARRAY()<\/code> function:<\/p>\n\n\n
JSON_ARRAY(value1, value2, \u2026)<\/code><\/span><\/pre>\n\n\n
In this syntax, you specify one or more values separated by commas such as value1<\/code>, value2<\/code>, …. These are values you want to include in the JSON array.<\/p>\n\n\n\n
The JSON_ARRAY()<\/code> function returns a JSON array from the input values.<\/p>\n\n\n\n
The JSON_ARRAY()<\/code> function is useful when you need to generate JSON arrays from existing data in your database.<\/p>\n\n\n\n
The MySQL JSON_ARRAY() function examples<\/h2>\n\n\n\n
Let’s take some examples of using the JSON_ARRAY()<\/code> function.<\/p>\n\n\n\n
1) Creating a JSON array<\/h3>\n\n\n\n
The following example uses the JSON_ARRAY()<\/code> function to create a JSON array:<\/p>\n\n\n
SELECT<\/span> \n    JSON_ARRAY('John'<\/span>,'Doe'<\/span>, 25<\/span>, 'MySQL Developer'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The query returns the following JSON array:<\/p>\n\n\n
+-------------------------------------------------+<\/span>\n| JSON_ARRAY('John','Doe', 25, 'MySQL Developer') |\n+-------------------------------------------------+<\/span>\n| [\"John\", \"Doe\", 25, \"MySQL Developer\"]          |\n+-------------------------------------------------+<\/span>\n1 row in set<\/span> (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
2) Creating a JSON array from table data<\/h3>\n\n\n\n
We’ll use the employees<\/code> table from the sample database<\/a> for the demonstration:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
The following example uses the JSON_ARRAY()<\/code> function to create a JSON array of the first name, email, and job title of employee numbers 1002 and 1056:<\/p>\n\n\n
SELECT<\/span> \n  JSON_ARRAY(firstName, email, jobTitle)\nFROM<\/span> \n  employees \nWHERE<\/span> \n  employeeNumber IN<\/span> (1002<\/span>, 1056<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+--------------------------------------------------------+<\/span>\n| JSON_ARRAY(firstName, email, jobTitle)                 |\n+--------------------------------------------------------+<\/span>\n| [\"Diane\", \"dmurphy@classicmodelcars.com\", \"President\"] |\n| [\"Mary\", \"mpatterso@classicmodelcars.com\", \"VP Sales\"] |\n+--------------------------------------------------------+<\/span>\n2 rows in set<\/span> (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
You can use the JSON_PRETTY()<\/code> function to format the JSON arrays as follows:<\/p>\n\n\n
SELECT<\/span> \n  JSON_PRETTY(\n    JSON_ARRAY(firstName, email, jobTitle)\n  ) \nFROM<\/span> \n  employees \nWHERE<\/span> \n  employeeNumber IN<\/span> (1002<\/span>, 1056<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+----------------------------------------------------------------+<\/span>\n| JSON_PRETTY(JSON_ARRAY(firstName, email, jobTitle))            |\n+----------------------------------------------------------------+<\/span>\n| [\n  \"Diane\",\n  \"dmurphy@classicmodelcars.com\",\n  \"President\"\n] |\n| [\n  \"Mary\",\n  \"mpatterso@classicmodelcars.com\",\n  \"VP Sales\"\n] |\n+----------------------------------------------------------------+<\/span>\n2 rows in set<\/span> (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
3) Storing JSON arrays into a table<\/h3>\n\n\n\n
First, create a table<\/a> for storing JSON data:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> contacts(\n    employeeNumber INT<\/span> PRIMARY KEY<\/span>,\n    data<\/span> JSON<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, create JSON arrays using the JSON_ARRAY()<\/code> function and store them in the contacts<\/code> table:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> contacts(employeeNumber, data<\/span>) \nSELECT<\/span> \n  employeeNumber, \n  JSON_ARRAY(firstName, extension, email) \nFROM<\/span> \n  employees;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, retrieve data from the contacts<\/code> table:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> contacts;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here’s the partial output:<\/p>\n\n\n
+----------------+---------------------------------------------------------+<\/span>\n| employeeNumber | data                                                    |\n+----------------+---------------------------------------------------------+<\/span>\n|           1002 | [\"Diane\", \"x5800\", \"dmurphy@classicmodelcars.com\"]      |\n|           1056 | [\"Mary\", \"x4611\", \"mpatterso@classicmodelcars.com\"]     |\n|           1076 | [\"Jeff\", \"x9273\", \"jfirrelli@classicmodelcars.com\"]     |\n|           1088 | [\"William\", \"x4871\", \"wpatterson@classicmodelcars.com\"] |\n...<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use the JSON_ARRAY()<\/code> function to create a JSON array from a list of values.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
In this example, we begin by using the JSON_ARRAY()<\/code> function to create a JSON array containing the first name, extension, and email of each employee. Subsequently, we use the INSERT INTO SELECT<\/a> statement to insert the data into the contacts<\/code> table.<\/p>\n\n\n\n