In MySQL 5.7, the JSON functionality was introduced that allows you to get dataset results in JSON data format, virtual columns, and, tentatively, 15 SQL functions that allow you to search and use JSON data on the server side. In MySQL 8, there are additional aggregation functions that can be used in JSON objects/arrays to represent loaded data in a further optimized way. The following are the two JSON aggregation functions that were introduced in MySQL 8:
JSON_OBJECTAGG()JSON_ARRAYAGG()
Let's take an example of an e-commerce platform where we will create a few tables and data to understand both these functions.
First, let's create a table for products as follows:
CREATE TABLE `products` ( `id` int(11) NOT NULL AUTO_INCREMENT, `pname` varchar(75) DEFAULT NULL, `pmanufacturer` varchar(75) DEFAULT NULL, `pprice` int(10) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Now let's create a table for attributes:
CREATE TABLE `attr` ( `id` int(11) NOT NULL AUTO_INCREMENT...