DB Software v7.8

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

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.

In this section: