MySQL - JSON_ARRAYAGG() Function


Advertisements

In general, aggregation is a consideration of a collection of objects that are bound together as a single entity.

MySQL provides a set of aggregate functions that perform operations on all the entities of the column of a table considering them as a single unit.

The MySQL JSON_ ARRAYAGG() function aggregates the contents of the specified column (or, given expression) as a single array. If the specified columns have no rows this function returns NULL.

Syntax

Following is the syntax of this function –

JSON_ARRAYAGG(expr);

Example 1

Assume we have created a table and populated it using the following queries –

mysql> CREATE TABLE Test(ID INT,value VARCHAR(100));
mysql> INSERT INTO Test values(1, '110001');
mysql> INSERT INTO Test values(2, '11101');
mysql> INSERT INTO Test values(2, '11100001');

Following query lists the entities of the column value, as a single JSON array –

mysql> SELECT JSON_ARRAYAGG(value) from test;
+---------------------------------+
|            JSON_ARRAYAGG(value) |
+---------------------------------+
| ["110001", "11101", "11100001"] |
+---------------------------------+
1 row in set (0.00 sec)

Example 2

Following is an example demonstrating the usage of this function. Assume we have created a table with name MyPlayers in MySQL database using CREATE statement as shown below –

mysql> CREATE TABLE MyPlayers(
	ID INT,
	First_Name VARCHAR(255),
	Last_Name VARCHAR(255),
	Socre_In_Exhibiiton_match INT,
	COUNTRY VARCHAR(100),
	PRIMARY KEY (ID)
);

This table stores the first and last names, country, scores in an exhibition match of a player. Now, we will insert 7 records in MyPlayers table using INSERT statements −

insert into MyPlayers values(1, 'Shikhar', 'Dhawan', 95, 'India');
insert into MyPlayers values(2, 'Jonathan', 'Trott', 50, 'SouthAfrica');
insert into MyPlayers values(3, 'Kumara', 'Sangakkara', 25, 'Sri Lanka');
insert into MyPlayers values(4, 'Virat', 'Kohli', 50, 'India');
insert into MyPlayers values(5, 'Rohit', 'Sharma', 25, 'India');
insert into MyPlayers values(6, 'Ravindra', 'Jadeja', 15, 'India');
insert into MyPlayers values(7, 'James', 'Anderson', 15, 'England');

Following query lists scores of all the players in an exhibition match in the form of a single JSON array –

mysql> SELECT JSON_ARRAYAGG(Socre_In_Exhibiiton_match) as Scores from MyPlayers;
+------------------------------+
|                       Scores |
+------------------------------+
| [95, 50, 25, 50, 25, 15, 15] |
+------------------------------+
1 row in set (0.00 sec)

You can also use GROUP BY clause with this function as shown below –

mysql> SELECT Country, JSON_ARRAYAGG(Socre_In_Exhibiiton_match) as Scores from MyPlayers group by country;
+-------------+------------------+
|     Country |           Scores |
+-------------+------------------+
| England     |             [15] |
|       India | [95, 50, 25, 15] |
| SouthAfrica |             [50] |
|   Sri Lanka |             [25] |
+-------------+------------------+
4 rows in set (0.00 sec)

Example 3

Following is another example of this function. Assume we have created another table named employee_tbl and inserted records in it as follows –

mysql> CREATE TABLE employee_tbl (
	id INT,
	name VARCHAR(255),
	Work_date INT,
	daily_typing_pages INT
);
mysql> insert into employee_tbl values(1, John, DATE('2007-01-24'), 250);
mysql> insert into employee_tbl values(2, 'Ram', DATE('2007-05-27'), 220);
mysql> insert into employee_tbl values(3, 'Jack', DATE('2007-05-06'), 170);
mysql> insert into employee_tbl values(3, 'Jack', DATE('2007-04-06'), 100);
mysql> insert into employee_tbl values(4, 'Jill', DATE('2007-04-06'), 220);
mysql> insert into employee_tbl values(5, 'Zara', DATE('2007-06-06'), 300);
mysql> insert into employee_tbl values(5, 'Zara', DATE('2007-02-06'), 350);

Now, suppose based on the above table you want to retrieve the daily typing pages of all the employees as a single JSON array you can do so as shown below −

mysql> SELECT JSON_ARRAYAGG(daily_typing_pages) FROM employee_tbl;
+-------------------------------------+
|   JSON_ARRAYAGG(daily_typing_pages) |
+-------------------------------------+
| [220, 170, 100, 220, 300, 350, 250] |
+-------------------------------------+
1 row in set (0.00 sec)

You can also list the JASON array for a grouped columns as shown below –

mysql> SELECT Name, JSON_ARRAYAGG(daily_typing_pages) FROM employee_tbl GROUP BY name;
+------+-----------------------------------+
| NAme | JSON_ARRAYAGG(daily_typing_pages) |
+------+-----------------------------------+
| Jack |                        [170, 100] |
| Jill |                             [220] |
| John |                             [250] |
|  Ram |                             [220] |
| Zara |                        [300, 350] |
+------+-----------------------------------+
5 rows in set (0.00 sec)

Example 4

Let us create a table named student and inserted records into it using CREATE and INSERT statements as shown below –

mysql> CREATE TABLE student (name VARCHAR(15), marks INT, grade CHAR);
mysql> INSERT INTO student VALUES ('Raju', 80, 'A');
mysql> INSERT INTO student VALUES ('Rahman', 60, 'B');
mysql> INSERT INTO student VALUES ('Robert', 45, 'C');

Following query prints the values of the marks column of the student table as a JSON array –

mysql> SELECT JSON_ARRAYAGG(marks) from student;
+----------------------+
| JSON_ARRAYAGG(marks) |
+----------------------+
|         [80, 60, 45] |
+----------------------+
1 row in set (0.00 sec)

Using the GROUP BY clause –

mysql> SELECT name, JSON_ARRAYAGG(marks) from student GROUP BY name;
+--------+----------------------+
|   name | JSON_ARRAYAGG(marks) |
+--------+----------------------+
| Rahman |                 [60] |
|   Raju |                 [80] |
| Robert |                 [45] |
+--------+----------------------+
3 rows in set (0.00 sec)

Example 5

Assume we have created and populated a table with name Sales.

mysql> CREATE TABLE sales(
	ID INT,
	ProductName VARCHAR(255),
	CustomerName VARCHAR(255),
	DispatchDate date,
	DeliveryTime time,
	Price INT,
	Location VARCHAR(255)
);
Query OK, 0 rows affected (2.22 sec)
INSERT INTO SALES values(1, 'Key-Board', 'Raja', DATE('2019-09-01'), TIME('11:00:00'), 7000, 'Hyderabad');
INSERT INTO SALES values(2, 'Earphones', 'Roja', DATE('2019-05-01'), TIME('11:00:00'), 2000, 'Vishakhapatnam');
INSERT INTO SALES values(3, 'Mouse', 'Puja', DATE('2019-03-01'), TIME('10:59:59'), 3000, 'Vijayawada');
INSERT INTO SALES values(4, 'Mobile', 'Vanaja', DATE('2019-03-01'), TIME('10:10:52'), 9000, 'Chennai');
INSERT INTO SALES values(5, 'Headset', 'Jalaja', DATE('2019-04-06'), TIME('11:08:59'), 6000, 'Goa');

Following query prints the JASON array consisting the price values –

mysql> SELECT JSON_ARRAYAGG(Price) FROM SALES;
+--------------------------------+
|           JSON_ARRAYAGG(Price) |
+--------------------------------+
| [7000, 2000, 3000, 9000, 6000] |
+--------------------------------+
1 row in set (0.00 sec)
mysql-aggregate-functions.htm
Advertisements