SingleStoreDB v8.1

JSON_AGG

JSON_AGG combines values and returns the combined data as a single JSON row.

NULL values are included in the aggregation.

Syntax

JSON_AGG(object [ ORDER BY expression [ ASC | DESC] [, expression [ ASC | DESC ] ...] ])

Argument

object: A constant, a column name, a table reference, or an expression.

Return Type

A JSON array.

Remarks

JSON_AGG is not supported as a window function.
By default, the length of JSON_AGG output must not be greater than 16MB. You can change this value using the json_agg_max_len engine variable.
The JSON_AGG function doesn’t group fields by itself. To group the aggregated columns, use the GROUP BY clause.

Examples

All the examples in the sections below use the following master-detail tables: employees and assets.

CREATE TABLE ja_employees(
emp_id numeric(5),
emp_lastname varchar(25),
emp_firstname varchar(25),
emp_title varchar(25),
PRIMARY KEY(emp_id)
);

INSERT INTO ja_employees VALUES('014', 'Bateman', 'Patrick', 'Mgr'),
('102', 'Karras', 'Damien', 'Doctor'),
('298', 'Denbrough', 'Bill', 'Writer'),
('399', 'Torrance', 'Jack', 'PR Dir'),
('410', 'Wilkes', 'Annie', 'HR');

DESC ja_employees;
****
+----------------+---------+------+----------+---------+-------+
| Field          | Type        | Null | Key  | Default | Extra |
+----------------+-------------+------+------+---------+-------+
| emp_id         | int(5)      | NO   | PRI  | 0       |       |
| emp_lastname   | varchar(25) | YES  |      | NULL    |       |
| emp_ firstname | varchar(25) | YES  |      | NULL    |       |
| emp_title      | varchar(25) | YES  |      | NULL    |       |
+----------------+-------------+------+------+---------+-------+

SELECT * FROM ja_employees;
****
+---------+--------------+---------------+-------------+
| emp_id  | emp_lastname | emp_firstname | emp_title   |
+---------+--------------+---------------+-------------+
| 102     | Karras       | Damien        | Doctor      |
| 298     | Denbrough    | Bill          | Writer      |
| 410     | Wilkes       | Annie         | HR          |
| 14      | Bateman      | Patrick       | Manager     |
| 399     | Torrance     | Jack          | PR Dir      |
+---------+--------------+---------------+-------------+

Asset table details:

CREATE TABLE ja_assets(
asset_id int,
asset_type varchar(50),
asset_desc varchar(50),
emp_id int(5),
PRIMARY KEY(emp_id)
);

INSERT INTO ja_assets VALUES('1049', 'laptop', 'mac_book_pro', '14'),
('49', 'cell_phone', 'iphone_12', '102'),
('1100', 'laptop', 'mac_book_pro', '298'),
('2037', 'laptop', 'mac_book_air', '399'),
('58', 'cell_phone', 'iphone_12', '410');

DESC ja_assets;
****
+-------------+-------------+------+------+---------+-------+
| Field       | Type        | Null | Key  | Default | Extra |
+-------------+-------------+------+------+---------+-------+
| asset_id    | int(5)      | YES  |      | NULL    |       |
| asset_type  | varchar(50) | YES  |      | NULL    |       |
| asset_dsc   | varchar(50) | YES  |      | NULL    |       |
| emp_id      | int(5)      | YES  |      | NULL    |       |
+-------------+-------------+------+------+---------+-------+

SELECT * FROM ja_assets;
****
+----------+------------+--------------+--------+
| asset_id | asset_type | asset_desc   | emp_id |
+----------+------------+--------------+--------+
| 49       | cell phone | iphone 12    | 102    |
| 2037     | laptop     | mac book air | 399    |
| 1049     | laptop     | mac book pro |  14    |
| 1100     | laptop     | mac book pro | 298    |
| 58       | cell phone | iphone 12    | 420    |
+----------+------------+--------------+--------+

Aggregating Columns Using JSON_AGG

The following examples use the product table, whose definition and contents are shown at the beginning of the Examples section.

Aggregating One Column

JSON_AGG can be used to aggregate (i.e., combine) JSON formatted data into one object. This returns a single row of JSON data. In the following example, the JSON_AGG function outputs the aggregated data of the emp_lastname column as a single JSON row.

SELECT JSON_AGG(emp_lastname) FROM ja_employees;
****
+-----------------------------------------------------+
| JSON_AGG(emp_lastname)                              |
+-----------------------------------------------------+
|["Karras","Denbrough","Wilkes","Bateman","Torrance"] |
+-----------------------------------------------------+

Aggregating All Columns

You can aggregate all the columns of a table using the table_name.* notation. In the following example, the JSON_AGG function outputs the aggregated data from all columns of the assetstable as a single JSON row.

SELECT JSON_AGG(ja_assets.*) FROM ja_assets;
****
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| JSON_AGG(ja_assets*)                                                                                                                                                 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|[{"asset_desc":"iphone_12","asset_id":49,"asset_type":"cell_phone","emp_id":102},{"asset_desc":"mac_book_air","asset_id":2037,"asset_type":"laptop","emp_id":399}, |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Note

The entire contents of the assets table will show in a single very long row. The results above were edited for readability.

Aggregating a Subset of Columns

JSON_AGG supports only a single argument, so queries like SELECT JSON_AGG(a,b) will return an error. The following example shows how to aggregate multiple columns using the WITH clause. This clause temporarily stores the output of the SELECT statement in a named result set, which can be referenced by the JSON_AGG function as a single argument.

WITH full_name AS
  ( SELECT emp_firstname, emp_lastname    
    FROM ja_employees  )
SELECT JSON_AGG(full_name.*)
FROM full_name;
****
+----------------------------------------------------------------------------------------------------------+
| JSON_AGG(full_name.*)                                                                                    |
+----------------------------------------------------------------------------------------------------------+
| [{"emp_firstname":"Damien","emp_lastname":"Karras"},{"emp_firstname":"Bill","emp_lastname":"Denbrough"}, |
{"emp_firstname":"Annie","emp_lastname":"Wilkes"},{"emp_firstname":"Patrick","emp_lastname":"Bateman"},    |
{"emp_firstname":"Jack","emp_lastname":"Torrance"}]                                                        |
+----------------------------------------------------------------------------------------------------------+

You can also use a row constructor and cast to a record to perform the same task.

SELECT JSON_AGG(ROW(emp_id, emp_lastname):>RECORD(emp_id INT, emp_lastname VARCHAR(25)))
FROM ja_employees;
****
+---------------------------------------------------------------+
| JSON_AGG(ROW(emp_id, emp_lastname):>RECORD(emp_id INT,        |
| emp_lastname VARCHAR(25)))                                    |
+---------------------------------------------------------------+
| [{"emp_id":102,"emp_lastname":"Karras"},                      |
| {"emp_id":298,"emp_lastname":"Denbrough"},                    |
| {"emp_id":410,"emp_lastname":"Wilkes"},                       |
| {"emp_id":14,"emp_lastname":"Bateman"},                       |
| {"emp_id":399,"emp_lastname":"Torrance"}]                     |
+---------------------------------------------------------------+

Grouping the Aggregated Columns Using the GROUP BY Clause

You can group aggregated columns using the GROUP BY clause. The following examples use the employee table, whose definition and contents are shown at the beginning of the Examples section.

SELECT emp_id, JSON_AGG(emp_lastname) FROM ja_employees GROUP BY emp_id;
****
+---------+------------------------+
| emp_id  | JSON_AGG(emp_lastname) |
+---------+------------------------+
| 399     | ["Torrance"]           |
| 102     | ["Karras"]             |
| 298     | ["Denbrough"]          |  
| 410     | ["Wilkes"]             |
|  14     | ["Bateman"]            |
+---------+------------------------+

You can also group results by some columns, and aggregate other columns as an array of JSON objects:

SELECT emp_lastname, JSON_AGG(ROW(emp_firstname, emp_id):>RECORD(emp_firstname varchar(25), emp_id JSON))
FROM ja_employees
GROUP BY emp_lastname;
****
+--------------+------------------------------------------------------------------------------+
| emp_lastname | JSON_AGG(ROW(emp_firstname, emp_id):>RECORD(emp_firstname varchar(25),       |
|              | emp_id JSON))JSON_AGG(ROW(ID,Description):>RECORD(ID INT, Description JSON)) |
+--------------+------------------------------------------------------------------------------+
| Denbrough    | [{"emp_firstname":"Bill","emp_id":298}]                                      |
| Torrance     | [{"emp_firstname":"Jack","emp_id":399}] ]                                    |
| Karras       | [{"emp_firstname":"Damien","emp_id":102}]                                    |
| Wilkes       | [{"emp_firstname":"Annie","emp_id":410}]                                     |
| Bateman      | [{"emp_firstname":"Patrick","emp_id":14}]                                    |
+--------------+------------------------------------------------------------------------------+

Arranging the Aggregated Columns Using the ORDER BY Clause

You can arrange the aggregated columns using the ORDER BY clause.

Example 1

The following example use the employee table, whose definition and contents are shown at the beginning of the Examples section.The example output displays the employees last names in order by the emp_id column.

SELECT JSON_AGG(emp_lastname order by emp_id) FROM ja_employees;

****
+-----------------------------------------------------+
| JSON_AGG(emp_lastname order by emp_id)              |
+-----------------------------------------------------+
|["Bateman","Karras","Denbrough","Torrance","Wilkes"] |
+-----------------------------------------------------+

Example 2

The following example uses a different dataset to illustrate a more complex query using both the ORDER BY and GROUP BY clauses in the same query.

CREATE TABLE ja_time_table(col_a int, col_b int, date_time datetime(6));

INSERT ja_time_table VALUES(1, 100, now(6));
INSERT ja_time_table VALUES(1, 150, now(6));
INSERT ja_time_table VALUES(1, 200, now(6));
INSERT ja_time_table VALUES(2, 500, now(6));
INSERT ja_time_table VALUES(2, 600, now(6));
INSERT ja_time_table VALUES(2, 550, now(6));

SELECT * FROM ja_time_table;

****
+-------+--------+----------------------------+
| col_a | col_b  | date_time                  |
+-------+--------+----------------------------+
| 2     | 500    | 2022-09-20 20:59:02.545860 |
| 2     | 550    | 2022-09-20 20:59:12.300889 |
| 1     | 200    | 2022-09-20 20:58:56.156296 |
| 2     | 600    | 2022-09-20 20:59:07.388167 |
| 1     | 100    | 2022-09-20 20:58:42.586573 |
| 1     | 150    | 2022-09-20 20:58:49.274466 |
+-------+--------+----------------------------+

SELECT col_a, JSON_AGG(ROW(col_b,date_time):>record(col_b INT, date_time DATETIME(6)) 
ORDER BY date_time) FROM ja_time_table GROUP BY col_a;

****
+-------+----------------------------------------------------------+
| col_a | JSON_AGG(ROW(col_b,date_time):>record(col_b INT,         |
|       |   date_time DATETIME(6)) ORDER BY date_time)             |
+-------+----------------------------------------------------------+
| 1     | [{"col_b":100,"date_time":"2022-09-20 20:58:42.586573"}, | 
|       |  {"col_b":150,"date_time":"2022-09-20 20:58:49.274466"}, |
|       |  {"col_b":200,"date_time":"2022-09-20 20:58:56.156296"}] |
|-------|----------------------------------------------------------|
| 2     | [{"col_b":500,"date_time":"2022-09-20 20:59:02.545860"}, |
|       |  {"col_b":600,"date_time":"2022-09-20 20:59:07.388167"}, |
|       |  {"col_b":550,"date_time":"2022-09-20 20:59:12.300889"}] |
+-------+----------------------------------------------------------+

Representing a Master-Detail Rowset in JSON Format

JSON_AGG can take a rowset that represents a master-detail relationship, and convert it into JSON format, as a JSON array of JSON objects. Each JSON object in the array will contain a master object and its details as a sub-array. The following examples use the employees table.

Aggregating Data from One Table

The example below uses the JSON_AGG function to aggregate the details of the master table employees as a JSON object and displays the details as a sub-array. The WITH clause temporarily stores the result set of the SELECT statement, which is then referenced by the function. This helps in simplifying complex sub-queries within the function.

Important

The JSON_AGG function returns only a single row of JSON data if the GROUP BY clause is not used.

WITH emp_list AS
  (SELECT emp_lastname, JSON_AGG(ROW(emp_title, emp_id):>RECORD(emp_title varchar(25), emp_id JSON)) AS "emps"
    FROM ja_employees
    GROUP BY emp_lastname )
SELECT JSON_AGG(emp_list.*)
FROM emp_list;

****
+---------------------------------------------------------------------------------+
| JSON_AGG(emp_list.*)                                                            |
+---------------------------------------------------------------------------------+
|[{"emp_lastname":"Denbrough","emps":[{"emp_id":298,"emp_title":"Writer"}]},      |
|{"emp_lastname":"Torrance","emps":[{"emp_id":399,"emp_title":"PR Dir"}]},        |
|{"emp_lastname":"Karras","emps":[{"emp_id":102,"emp_title":"Doctor"}]},          |
|{"emp_lastname":"Wilkes","emps":[{"emp_id":410,"emp_title":"HR"}]},              |
|{"emp_lastname":"Bateman","emps":[{"emp_id":14,"emp_title":"Manager"}]}]         |
+--------------+------------------------------------------------------------------+

Note: The output is tab-spaced to increase readability, and the spacing may differ from the actual output.

Aggregating Single/Scalar Detail from Multiple Tables

In the following example, the JSON_AGG function aggregates the column data from both the employees and assets tables and displays the results as a single row of JSON data.

SELECT JSON_AGG(details.*) FROM (SELECT e.emp_id AS emp_id, JSON_AGG(asset_type)
AS master_list FROM ja_employees a JOIN ja_assets e on a.emp_id group by e.emp_id) as details;

****
+----------------------------------------------------------------------+
| JSON_AGG(details.*)                                                  |
+----------------------------------------------------------------------+
| [{"emp_id":102,"master_list":                                        |
| ["cell_phone","cell_phone","cell_phone","cell_phone","cell_phone"]}, |
| {"emp_id":410,"master_list":["cell_phone","cell_phone","cell_phone", |
| "cell_phone","cell_phone"]},{"emp_id":298,"master_list":["laptop",   |
| "laptop","laptop","laptop","laptop"]},{"emp_id":399,"master_list":   |
| ["laptop","laptop","laptop","laptop","laptop"]},{"emp_id":14,        |
| "master_list":["laptop","laptop","laptop","laptop","laptop"]}].      |
+----------------------------------------------------------------------+

Aggregating Multiple Details from Multiple Tables

In the following example, the JSON_AGG function aggregates data from multiple columns of different tables and returns the output as a JSON array.

SELECT JSON_AGG(details.*) FROM (SELECT a.emp_id AS emp_id, JSON_AGG(TO_JSON(e.*)) 
AS master_list FROM ja_assets e JOIN ja_employees a ON e.emp_id = a.emp_id GROUP BY a.emp_id) AS details;

****
+------------------------------------------------------------+
| JSON_AGG(details.*)                                        |
+------------------------------------------------------------+
| [{"emp_id":410,"master_list":[{"asset_desc":"iphone_12",   |
| "asset_id":58,"asset_type":"cell_phone","emp_id":410}]},   |
| {"emp_id":298,"master_list":[{"asset_desc":"mac_book_pro", |
| "asset_id":1100,"asset_type":"laptop","emp_id":298}]},     |
| {"emp_id":399,"master_list":[{"asset_desc":"mac_book_air", |
| "asset_id":2037,"asset_type":"laptop","emp_id":399}]},     |
| {"emp_id":14,"master_list":[{"asset_desc":"mac_book_pro",  |
| "asset_id":1049,"asset_type":"laptop","emp_id":14}]},      |
| {"emp_id":102,"master_list":[{"asset_desc":"iphone_12",    |
| "asset_id":49,"asset_type":"cell_phone","emp_id":102}]}]   |
+------------------------------------------------------------+

Aggregating by Grouping Using Concat

Results can be grouped by using concat in the query. The following example uses a shortened version of the assets table above.

SELECT JSON_AGG(CONCAT(asset_id, '-', asset_desc, '-', emp_id) ORDER BY emp_id DESC, asset_id ASC) FROM ja_assets;

****
+----------------------------------------------------------------------------------------------------------------+
| JSON_AGG (CONCAT(asset_id, '-', asset_desc, '-', emp_id)                                                       |
|  ORDER BY emp_id DESC, asset_id ASC)JSON_AGG(details.*)                                                        |
+----------------------------------------------------------------------------------------------------------------+
| ["58-iphone_12-410","2037-mac_book_air-399","1100-mac_book_pro-298","49-iphone_12-102","1049-mac_book_pro-14"] |
+----------------------------------------------------------------------------------------------------------------+

Using a JOIN on an Array with JSON_AGG

The following example will show how to use a JOIN on a JSON array. When using a JOIN the DISTINCT clause should be used in the Common Table Expression (CTE).

CREATE TABLE ja_emp_locations (emp_name text, emp_locations json);

INSERT INTO ja_emp_locations VALUES('Wilkes', '["Veracruz", "Tampa", "Bismarck"]'),
('Wilkes', '["San Diego","Los Angeles"]'), ('Wilkes', '["Downey"]'),
('Wilkes', '["Burbank","Pasadena"]'), ('Bateman', '["Maryland","DC"]'),
('Bateman', '["Boise","Seattle"]'), ('Wilkes', '["Las Vegas","Lake Tahoe"]'),
('Bateman', '["Philadelphia"]'), ('Bateman', '["Newark","Wilmington"]'),
('Bateman', '["Bozeman"]'), ('Bateman', '["Casper"]');

SELECT * FROM ja_emp_locations;

****
+----------+---------------------------------+
| emp_name | emp_locations                   |
+----------+---------------------------------+
| Bateman  | ["Casper"]                      |
| Wilkes   | ["San Diego","Los Angeles"]     |
| Wilkes   | ["Veracruz","Tampa","Bismarck"] |
| Wilkes   | ["Burbank","Pasadena"]          |
| Bateman  | ["Maryland","DC"]               |
| Bateman  | ["Philadelphia"]                |
| Wilkes   | ["Las Vegas","Lake Tahoe"]      |
| Wilkes   | ["Downey"]                      |
| Bateman  | ["Bozeman"]                     |
| Bateman  | ["Newark","Wilmington"]         |
| Bateman  | ["Boise","Seattle"]             |
+----------+---------------------------------+

WITH emp_locs AS (SELECT DISTINCT emp_name, table_col AS emp_locations
  FROM ja_emp_locations
         JOIN TABLE(JSON_TO_ARRAY(emp_locations)))
SELECT emp_name, json_agg(emp_locations) AS Locations
FROM emp_locs
GROUP BY emp_name;

****
+----------+------------------------------------------------------------------------------------------------------------------+
| emp_name | Locations                                                                                                        |
+----------+------------------------------------------------------------------------------------------------------------------+
| Wilkes   | ["San Diego","Los Angeles","Veracruz","Tampa","Bismarck","Las Vegas","Lake Tahoe","Burbank","Pasadena","Downey"] |
| Bateman  | ["Boise","Seattle","Bozeman","Maryland","DC","Casper","Newark","Wilmington","Philadelphia"]                      |
+----------+------------------------------------------------------------------------------------------------------------------+

In this section: