JSON_AGG
Aggregates values and returns the aggregated data as a single row containing JSON.
NULL
values are included in the aggregation.
Syntax
JSON_AGG (object)
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.There is no predefined ordering scheme applied to the results. Furthermore,
JSON_AGG
does not support theORDER BY
clause.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
The examples below use the following master-detail tables. Here’s the master table:
DESC product_quantity; **** +----------+---------+------+------+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------+------+------+---------+-------+ | ID | int(11) | NO | PRI | NULL | | | P_ID | int(11) | YES | | NULL | | | Quantity | int(11) | YES | | NULL | | +----------+---------+------+------+---------+-------+
SELECT * FROM product_quantity; **** +----+------+----------+ | ID | P_ID | Quantity | +----+------+----------+ | 1 | 1 | 2914 | | 2 | 2 | 3532 | | 3 | 3 | 2268 | +----+------+----------+
And, here’s the corresponding detail table:
DESC product; **** +-------------+-------------+------+------+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+------+---------+-------+ | ID | int(11) | NO | PRI | NULL | | | Tag | varchar(10) | YES | | NULL | | | Description | JSON | YES | | NULL | | +-------------+-------------+------+------+---------+-------+
SELECT * FROM product; **** +----+------+------------------------------------------------+ | ID | Tag | Description | +----+------+------------------------------------------------+ | 1 | US | {"description":[{"Name":"Ark","UID":"ghx"}]} | | 2 | UK | {"description":[{"Name":"Canon","UID":"yad"}]} | | 3 | US | {"description":[{"Name":"Peak","UID":"cmk"}]} | +----+------+------------------------------------------------+
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 JSON formatted data into one object. This returns a single row of JSON data.
SELECT JSON_AGG(Description) FROM product; **** +---------------------------------------------------------------------------------------------------------------------------------------------+ | JSON_AGG(Description) | +---------------------------------------------------------------------------------------------------------------------------------------------+ | [{"description":[{"Name":"Canon","UID":"yad"}]},{"description":[{"Name":"Ark","UID":"ghx"}]},{"description":[{"Name":"Peak","UID":"cmk"}]}] | +---------------------------------------------------------------------------------------------------------------------------------------------+
Aggregating all columns
You can aggregate all the columns of a table using the table_name.*
notation.
SELECT JSON_AGG(product.*) FROM product; **** +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | JSON_AGG(product.*) | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | [{"Description":{"description":[{"Name":"Canon","UID":"yad"}]},"ID":2,"Tag":"UK"},{"Description":{"description":[{"Name":"Ark","UID":"ghx"}]},"ID":1,"Tag":"US"},{"Description":{"description":[{"Name":"Peak","UID":"cmk"}]},"ID":3,"Tag":"US"}] | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Aggregating a subset of columns
JSON_AGG
supports only a single argument, hence queries like SELECT JSON_AGG(a,b)
return an error. The following example shows how to aggregate multiple columns using the WITH
clause:
WITH temp AS ( SELECT ID, Tag FROM product ) SELECT JSON_AGG(temp.*) FROM temp; **** +---------------------------------------------------------------+ | JSON_AGG(temp.*) | +---------------------------------------------------------------+ | [{"ID":2,"Tag":"UK"},{"ID":1,"Tag":"US"},{"ID":3,"Tag":"US"}] | +---------------------------------------------------------------+
You can also use a row constructor and cast to a record to perform the same task.
SELECT JSON_AGG(ROW(ID,Tag):>RECORD(ID INT, Tag VARCHAR(20))) FROM product; **** +---------------------------------------------------------------+ | JSON_AGG(ROW(ID,Tag):>RECORD(ID INT, Tag VARCHAR(20))) | +---------------------------------------------------------------+ | [{"ID":2,"Tag":"UK"},{"ID":1,"Tag":"US"},{"ID":3,"Tag":"US"}] | +---------------------------------------------------------------+
Grouping the Aggregated Columns with the GROUP BY Clause
You can group aggregated columns using the GROUP BY
clause. The following examples use the product
table, whose definition and contents are shown at the beginning of the Examples section.
SELECT Tag, JSON_AGG(ID) FROM product GROUP BY Tag; **** +------+--------------+ | Tag | JSON_AGG(ID) | +------+--------------+ | US | [1,3] | | UK | [2] | +------+--------------+
You can also group results by some columns, and aggregate other columns as an array of JSON objects as:
SELECT Tag, JSON_AGG(ROW(ID,Description):>RECORD(ID INT, Description JSON)) FROM product GROUP BY Tag; **** +------+--------------------------------------------------------------------------------------------------------------------------------------------+ | Tag | JSON_AGG(ROW(ID,Description):>RECORD(ID INT, Description JSON)) | +------+--------------------------------------------------------------------------------------------------------------------------------------------+ | US | [{"Description":{"description":[{"Name":"Ark","UID":"ghx"}]},"ID":1},{"Description":{"description":[{"Name":"Peak","UID":"cmk"}]},"ID":3}] | | UK | [{"Description":{"description":[{"Name":"Canon","UID":"yad"}]},"ID":2}] | +------+--------------------------------------------------------------------------------------------------------------------------------------------+
Representing a Master-Detail Rowset in JSON Format
JSON_AGG
can be used to represent a master-detail relationship in a single rowset and transform it into a JSON array of JSON objects, each containing a master object with its details as a sub-array. The following examples use the product
and product_quantity
tables, whose definition and contents are shown at the beginning of the Examples section.
Aggregating Data from One Table
WITH temp AS ( SELECT Tag, JSON_AGG(ROW(ID,Description):>RECORD(ID INT, Description JSON)) AS "DT" FROM product GROUP BY Tag ) SELECT JSON_AGG(temp.*) FROM temp; **** +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | JSON_AGG(temp.*) | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | [ { "DT": [ { "Description":{"description":[{"Name":"Ark","UID":"ghx"}]}, "ID":1 }, { "Description":{"description":[{"Name":"Peak","UID":"cmk"}]}, "ID":3 } ], "Tag":"US" }, { "DT": [ { "Description":{"description":[{"Name":"Canon","UID":"yad"}]}, "ID":2 } ], "Tag":"UK" } ] | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
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
SELECT JSON_AGG(details.*) FROM (SELECT p.Tag AS Tag, JSON_AGG(s.Quantity) AS Inventory FROM product_quantity s JOIN product p ON s.P_ID = p.ID GROUP BY p.Tag) AS details; **** +------------------------------------------------------------------------+ | JSON_AGG(details.*) | +------------------------------------------------------------------------+ | [{"Inventory":[3532],"Tag":"UK"},{"Inventory":[2914,2268],"Tag":"US"}] | +------------------------------------------------------------------------+
Aggregating Multiple Details from Multiple Tables
SELECT JSON_AGG(details.*) FROM (SELECT p.Tag AS Tag, JSON_AGG(TO_JSON(s.*)) AS Inventory FROM product_quantity s JOIN product p ON s.P_ID = p.ID GROUP BY p.Tag) AS details; **** [ { "Tag":"US", "Inventory": [ {"ID":1,"P_ID":1,"Quantity":2914}, {"ID":3,"P_ID":3,"Quantity":2268} ] }, { "Tag":"UK", "Inventory": [ {"ID":2,"P_ID":2,"Quantity":3532} ], } ]
Note: The output is tab-spaced to increase readability, and the spacing may differ from the actual output.