MySQL - JSON_OBJECTAGG() 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_OBJECTAGG() function accepts the names of two columns of a table as arguments and returns a JSON object as a result which have the entities of first argument as keys and the entities of the second argument as values.

If the specified columns have no rows this function returns NULL.

Syntax

Following is the syntax of this function –

JSON_OBJECTAGG(key, value);

Example 1

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'); MySQL JSON_OBJECTAGG() Function
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 OBJECT with key-value pairs with first name of the players as key and score as value –

mysql> SELECT JSON_OBJECTAGG(First_Name, Socre_In_Exhibiiton_match) as Scores from MyPlayers;
+------------------------------------------------------------------------------------------------------+
|                                                                                               Scores |
+------------------------------------------------------------------------------------------------------+
| {"James": 15, "Rohit": 25, "Virat": 50, "Kumara": 25, "Shikhar": 95, "Jonathan": 50, "Ravindra": 15} |
+------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Following query groups the scores of the players, based on the country –

mysql> SELECT Country, JSON_OBJECTAGG(First_Name, Socre_In_Exhibiiton_match) as Scores from MyPlayers GROUP BY country;
+-------------+-----------------------------------------------------------+
|     Country |                                                    Scores |
+-------------+-----------------------------------------------------------+
|     England |                                             {"James": 15} |
|       India | {"Rohit": 25, "Virat": 50, "Shikhar": 95, "Ravindra": 15} |
| SouthAfrica |                                          {"Jonathan": 50} |
|   Sri Lanka |                                            {"Kumara": 25} |
+-------------+-----------------------------------------------------------+
4 rows in set (0.00 sec)

Example 2

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 object you can do so as shown below −

mysql> SELECT JSON_OBJECTAGG(name, daily_typing_pages) FROM employee_tbl;
+------------------------------------------------------------------+
|                         JSON_OBJECTAGG(name, daily_typing_pages) |
+------------------------------------------------------------------+
| {"Ram": 220, "Jack": 100, "Jill": 220, "John": 250, "Zara": 350} |
+------------------------------------------------------------------+
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_OBJECTAGG(name, daily_typing_pages) FROM employee_tbl GROUP BY name;
+------+------------------------------------------+
| Name | JSON_OBJECTAGG(name, daily_typing_pages) |
+------+------------------------------------------+
| Jack |                            {"Jack": 100} |
| Jill |                            {"Jill": 220} |
| John |                            {"John": 250} |
|  Ram |                             {"Ram": 220} |
| Zara |                            {"Zara": 350} |
+------+------------------------------------------+
5 rows in set (0.00 sec)

Example 3

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 object –

mysql> SELECT JSON_OBJECTAGG(name, marks) from student;
+------------------------------------------+
|              JSON_OBJECTAGG(name, marks) |
+------------------------------------------+
| {"Raju": 80, "Rahman": 60, "Robert": 45} |
+------------------------------------------+
1 row in set (0.00 sec)

Using the GROUP BY clause –

mysql> SELECT JSON_OBJECTAGG(name, marks) from student GROUP BY name;
+-----------------------------+
| JSON_OBJECTAGG(name, marks) |
+-----------------------------+
|              {"Rahman": 60} |
|                {"Raju": 80} |
|              {"Robert": 45} |
+-----------------------------+
3 rows in set (0.00 sec)

Example 4

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 object consisting the price values –

mysql> SELECT JSON_OBJECTAGG(ProductName, Price) FROM SALES;
+----------------------------------------------------------------------------------------+
|                                                     JSON_OBJECTAGG(ProductName, Price) |
+----------------------------------------------------------------------------------------+
| {"Mouse": 3000, "Mobile": 9000, "Headset": 6000, "Earphones": 2000, "Key-Board": 7000} |
+----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

You can also re write the above query as shown below –

mysql> SELECT JSON_OBJECTAGG(ProductName, Price) FROM SALES GROUP BY ProductName;
+------------------------------------+
| JSON_OBJECTAGG(ProductName, Price) |
+------------------------------------+
|                {"Earphones": 2000} |
|                  {"Headset": 6000} |
|                {"Key-Board": 7000} |
|                   {"Mobile": 9000} |
|                    {"Mouse": 3000} |
+------------------------------------+
5 rows in set (0.00 sec)
mysql-aggregate-functions.htm
Advertisements