Nesting JSON Functions Inside JSON_ARRAYAGG() And JSON_OBJECTAGG() In MySQL 5.7.38

The other day, Joel Hill and I were pair-programming on some complex SQL queries, attempting to figure out how to group rows while simultaneously limiting the aggregation and extracting portions of it. I don’t believe that what we were trying to do was possible within a single query (at least not in MySQL 5.7); but, it got me noodling on JSON aggregation. I’ve looked at using JSON_ARRAYGG() and JSON_OBJECTAGG() in the past; but, I wanted to follow-up with some inspiration from Scott Steinbeck on nesting JSON functions inside JSON aggregates in MySQL 5.7.39.

When using the GROUP BY in MySQL to collapse rows, the JSON_ARRAYAGG() and JSON_OBJECTAGG() functions can be used to roll-up grouped values into a single data-point. The JSON_ARRAYAGG() function creates an array literal and the JSON_OBJECTAGG() function creates an object literal.

I’ve only dabbled in these functions so far. And, when I do, I normally use column names to invoke these functions. But, as Steinbeck mentioned in the comments of my previous post, we can use JSON expressions inside our JSON aggregates. What this means is that we can extract multiple columns from each row within our grouped records.

To explore this concept, let’s create a MySQL database table that has user phone numbers. Each user can have zero-or-more phone numbers:

CREATE TABLE `contact` (
	`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
	`userID` int(10) unsigned NOT NULL,
	`phoneNumber` varchar(20) NOT NULL,
	`phoneExt` varchar(10) NOT NULL,
	`isPrimary` tinyint(3) unsigned NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO
	contact
VALUES
	-- User 1.
	( 1, 1, '212-555-1234', 'x1', 1 ),
	( 2, 1, '212-555-8829', '', 0 ),
	( 3, 1, '212-555-9400', '', 0 ),
	( 4, 1, '212-555-1527', '', 0 ),
	-- User 2.
	( 5, 2, '917-555-5532', '', 1 ),
	-- User 3.
	( 6, 3, '508-555-9370', '', 1 ),
	( 7, 3, '508-555-7009', '', 0 )
;

Now, to showcase the nesting of JSON functions and aggregates, we’re going to GROUP BY the userID and then extract information about all the phone numbers associated with each user. In the following SQL, I’m using the JSON_ARRAYAGG() to return records as an array; and, I’m using the JSON_OBJECTAGG() to return records as an object:

SELECT
	userID,
	-- Traditional aggregates return a single value.
	COUNT( * ) AS phoneCount,
	-- JSON ARRAY aggregate returns a single value; but, it contains a multitude of cross-
	-- row values within it. In this case, each item within the array is constructed by
	-- plucking properties from each collapsed row within the current grouping.
	JSON_ARRAYAGG(
		JSON_OBJECT(
			'id', id,
			'phoneNumber', phoneNumber,
			'phoneExt', phoneExt,
			'isPrimary', isPrimary
		)
	) AS records,
	-- JSON OBJECT aggregate returns a single value; but, it contains a multitude of cross-
	-- row values within it. In this case, each entry within the object is constructed by
	-- using the 'id' of each collapsed row within the current grouping; and, the associated
	-- value is created as a separate JSON object that plucks columns from the relevant row.
	JSON_OBJECTAGG(
		id,
		JSON_OBJECT(
			'id', id,
			'phoneNumber', phoneNumber,
			'phoneExt', phoneExt,
			'isPrimary', isPrimary
		)
	) AS recordsIndex
FROM
	contact
GROUP BY
	userID
;

In this MySQL query, the JSON_OBJECT() calls are being performed on a per-row basis for each row within the grouping. The JSON_ARRAYAGG() and JSON_OBJECTAGG() then take those results and roll them up into a single value per grouping (as an array literal or object literal, respectively). And so, when we run this SQL, we get the following output:

As you can see, we get 3-rows back in our GROUP BY MySQL query (1 row per user). And, within each row, we were able to also extract all of the phone numbers associated with each grouped user! That’s pretty cool.

Want to use code from this post?
Check out the license.