Skip to main content
Ben Nadel at CFUNITED 2010 (Landsdown, VA) with: Tim Cunningham
Ben Nadel at CFUNITED 2010 (Landsdown, VA) with: Tim Cunningham ( @TimCunningham71 )

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

Published in Comments (5)

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,

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


	-- 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.
			'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.
			'id', id,
			'phoneNumber', phoneNumber,
			'phoneExt', phoneExt,
			'isPrimary', isPrimary
	) AS recordsIndex

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.

Reader Comments


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



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

I believe in love. I believe in compassion. I believe in human rights. I believe that we can afford to give more of these gifts to the world around us because it costs us nothing to be decent and kind and understanding. And, I want you to know that when you land on this site, you are accepted for who you are, no matter how you identify, what truths you live, or whatever kind of goofy shit makes you feel alive! Rock on with your bad self!
Ben Nadel