# JSON\_AGG

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

`NULL` values are included in the aggregation.

## Syntax

```sql
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.
* Can be used for similar tasks as [GROUP\_CONCAT](https://docs.singlestore.com/db/v9.1/reference/sql-reference/aggregate-functions/group-concat.md) and both commands support the ORDER BY clause.
* 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.

```sql
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;

```

```output

+----------------+---------+------+----------+---------+-------+
| 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    |       |
+----------------+-------------+------+------+---------+-------+

```

```sql
SELECT * FROM ja_employees;

```

```output

+---------+--------------+---------------+-------------+
| 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:

```sql
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;

```

```output

+-------------+-------------+------+------+---------+-------+
| 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    |       |
+-------------+-------------+------+------+---------+-------+

```

```sql
SELECT * FROM ja_assets;

```

```output

+----------+------------+--------------+--------+
| 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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/json-functions/json-agg.md) 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.

```sql
SELECT JSON_AGG(emp_lastname) FROM ja_employees;

```

```output

+-----------------------------------------------------+
| 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 `assets`table as a single JSON row.

```sql
SELECT JSON_AGG(ja_assets.*) FROM ja_assets;

```

```output

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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 ](https://docs.singlestore.com/db/v9.1/query-data/advanced-query-topics/with-common-table-expressions.md) 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.

```sql
WITH full_name AS
  ( SELECT emp_firstname, emp_lastname    
    FROM ja_employees  )
SELECT JSON_AGG(full_name.*)
FROM full_name;

```

```output

+----------------------------------------------------------------------------------------------------------+
| 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.

```sql
SELECT JSON_AGG(ROW(emp_id, emp_lastname):>RECORD(emp_id INT, emp_lastname VARCHAR(25)))
FROM ja_employees;

```

```output

+---------------------------------------------------------------+
| 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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/json-functions/json-agg.md) section.

```sql
SELECT emp_id, JSON_AGG(emp_lastname) FROM ja_employees GROUP BY emp_id;

```

```output

+---------+------------------------+
| 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:

```sql
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;

```

```output

+--------------+------------------------------------------------------------------------------+
| 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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/json-functions/json-agg.md) section.The example output displays the employees last names in order by the emp\_id column.

```sql
SELECT JSON_AGG(emp_lastname order by emp_id) FROM ja_employees;


```

```output

+-----------------------------------------------------+
| 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;


```

```output

+-------+--------+----------------------------+
| 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 |
+-------+--------+----------------------------+

```

```sql
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;


```

```output

+-------+----------------------------------------------------------+
| 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](https://docs.singlestore.com/db/v9.1/query-data/advanced-query-topics/with-common-table-expressions.md) 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.

```sql
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;


```

```output

+---------------------------------------------------------------------------------+
| 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.

```sql
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;


```

```output

+----------------------------------------------------------------------+
| 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.

```sql
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;


```

```output

+------------------------------------------------------------+
| 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.

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


```

```output

+----------------------------------------------------------------------------------------------------------------+
| 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).

```sql
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;


```

```output

+----------+---------------------------------+
| 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"]             |
+----------+---------------------------------+

```

```sql
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;


```

```output

+----------+------------------------------------------------------------------------------------------------------------------+
| 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"]                      |
+----------+------------------------------------------------------------------------------------------------------------------+
```

***

Modified at: June 16, 2023

Source: [/db/v9.1/reference/sql-reference/json-functions/json-agg/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/json-functions/json-agg/)

(An index of the documentation is available at /llms.txt)