MySQL - JSON_ARRAYAGG() Function

MySQL JSON_ARRAYAGG() function aggregates the contents of the specified column (or, given expression) as a single JSON array.

A JSON array, like any simple array in other programming languages, can stores values of various datatypes like string, array, boolean, number, object, or NULL. The array can either consist of zero,one or more ordered values separated by commas, and enclosed within square brackets.

The JSON_ARRAYAGG() function combines the values in a column of a table into one such JSON array; however, the order of these values is undefined (so, they can be displayed in any order). If the specified columns have no rows this function returns NULL.

Syntax

Following is the syntax of MySQL JSON_ARRAYAGG() function −

JSON_ARRAYAGG(expr);

Parameters

This method accepts a parameter. The same is described below −

  • expr: An expression or column whose values are aggregated into a JSON array.

Return value

This function returns a JSON array containing the aggregated values. If there are no matching rows, it returns NULL.

Example

In the following query, we are creating a table named CUSTOMERS using the CREATE TABLE statement as follows −

CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

The below query adds 7 records into the above created table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

To verify whether the records are inserted, execute the following query −

Select * From CUSTOMERS;

Following is the CUSTOMERS table −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 Hyderabad 4500.00
7 Muffy 24 Indore 10000.00

Here, we are using the MySQL JSON_ARRAYAGG() function to list the entities of the column SALARY, as a single JSON array −

SELECT JSON_ARRAYAGG(SALARY) As Result
From CUSTOMERS;

This will produce the following result −

Result
[ 2000.00, 1500.00, 2000.00, 6500.00, 8500.00, 4500.00, 10000.00 ]

Following query groups the salaries of the customers, based on the AGE −

SELECT AGE, JSON_ARRAYAGG(SALARY) As Salaries From CUSTOMERS 
GROUP BY AGE;

This will produce the following result −

AGE Salaries
22 [4500.00]
23 [2000.00]
24 [10000.00]
25 [1500.00, 6500.00]
27 [8500.00]
32 [2000.00]
Advertisements