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.

Short link: https://bennadel.com/4350

Reader Comments

Frédéric Fortier Nov 8, 2022 at 2:59 PM

16 Comments

Another day where Ben teaches me something.

Ben Nadel Nov 8, 2022 at 3:39 PM

15,848 Comments

@Frédéric,

Ha ha, always glad to learn in public for this very reason 💪

Ben Nadel Dec 1, 2022 at 1:47 PM

15,848 Comments

This nested use of JSON_ARRAYAGG() and JSON_OBJECT() can be used in concert with LATERAL joins in MySQL 8 to run some pretty interesting queries. In this post, I'm using it to gather the "Top N" rows per each row in a group:

www.bennadel.com/blog/4362-using-lateral-joins-to-get-top-n-records-from-each-group-in-mysql-8-0-14.htm

This "Top N" concept has, historically, been very challenging in SQL.

Nithin Jan 19, 2023 at 10:57 AM

1 Comments

While trying to store data into table using JSON_ARRAYAGG, it wraps each value of array with double quotes. How to overcome this?

Ben Nadel Jan 19, 2023 at 11:00 AM

15,848 Comments

@Nithin,

So, are you running an aggregate and then storing it into another table with something like an INSERT INTO ... SELECT statement? I've only used JSON_ARRAYAGG() to pull data out of a table; I haven't used it to store data into a table.

That said, it really shouldn't be adding quotes to anything, as far as I've seen. In fact, I've a few times had to use JSON_QUOTE() to force MySQL to add quotes around a value that I was using in a JSON_CONTAINS() call.

Post A Comment — ❤️ I'd Love To Hear From You! ❤️

Post a Comment →