Master MySQL 8.0 JSON Aggregation: JSON_ARRAYAGG & JSON_OBJECTAGG Explained

MySQL is rapidly evolving and the upcoming 8.0 version adds many new features.

In 5.7 JSON support was introduced but processing capabilities were limited; MySQL 8.0 strengthens this with two new JSON aggregation functions:

JSON_ARRAYAGG()
JSON_OBJECTAGG()

These functions allow direct aggregation of data into JSON structures within SQL.

Basic Usage

Create a test table

CREATE TABLE `t1` (
  `key` varchar(8) DEFAULT NULL,
  `grp` varchar(8) DEFAULT NULL,
  `val` varchar(8)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Insert test data

INSERT INTO t1(`key`, `grp`, `val`) VALUES
("key1", "g1", "v1"),
("key2", "g1", "v2"),
("key3", "g2", "v3");

Use the aggregation functions in queries

Aggregate the column key into a JSON array:

SELECT JSON_ARRAYAGG(`key`) AS `keys` FROM t1;
-- Result: ["key1","key2","key3"]

Group aggregation:

SELECT grp, JSON_ARRAYAGG(`key`) AS `keys_grouped`
FROM t1 GROUP BY grp;
-- Result:
-- g1: ["key1","key2"]
-- g2: ["key3"]

SELECT grp, JSON_OBJECTAGG(`key`, val) AS `key_val_grouped`
FROM t1 GROUP BY grp;
-- Result:
-- g1: {"key1":"v1","key2":"v2"}
-- g2: {"key3":"v3"}

Aggregate two columns into a JSON object:

SELECT JSON_OBJECTAGG(`key`, val) AS `key_val` FROM t1;
-- Result: {"key1":"v1","key2":"v2","key3":"v3"}

Scenario Example

Description

Consider a product table with common attributes (name, price) and specific attributes (e.g., CPU for computers, color for clothing). Two additional tables store extended attributes and their values.

Requirement

Query all product information, including all attributes and their values, and aggregate the result into a JSON structure.

Implementation

Table definitions:

// Product table
CREATE TABLE `product` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(120) DEFAULT NULL,
  `manufacturer` varchar(120) DEFAULT NULL,
  `price` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

// Attribute table
CREATE TABLE `attribute` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(120) DEFAULT NULL,
  `description` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

// Value table
CREATE TABLE `value` (
  `prod_id` int(11) NOT NULL,
  `attribute_id` int(11) NOT NULL,
  `value` text,
  PRIMARY KEY (`prod_id`,`attribute_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Insert sample data (attributes, products, and their values) – omitted for brevity.

Query to aggregate into JSON:

SELECT
  JSON_OBJECT(
    "key", p.id,
    "title", p.name,
    "manufacturer", p.manufacturer,
    "price", p.price,
    "specifications", JSON_OBJECTAGG(a.name, v.value)
  ) AS product
FROM product AS p
JOIN value AS v ON p.id = v.prod_id
JOIN attribute AS a ON a.id = v.attribute_id
GROUP BY v.prod_id;

Sample result:

{
  "key": 1,
  "price": 26,
  "title": "LED Desk Lamp",
  "manufacturer": "X",
  "specifications": {
    "color": "black",
    "style": "classic",
    "usage": "Indoor use only",
    "material": "plastic",
    "bulb_type": "LED"
  }
}
...

Conclusion

This article is compiled from MySQL Server Team (http://mysqlserverteam.com/mysql-8-0-labs-json-aggregation-functions). MySQL 8 experimental version can be downloaded from https://labs.mysql.com/ (choose MySQL Server 8.0.0 Optimizer).