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 thejson_agg_max_len
engine variable.The
JSON_AGG
function doesn’t group fields by itself. To group the aggregated columns, use theGROUP 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 assets
table 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"] | +----------+------------------------------------------------------------------------------------------------------------------+