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_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_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_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.
Another day where Ben teaches me something.
Ha ha, always glad to learn in public for this very reason 💪
This nested use of
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:
This "Top N" concept has, historically, been very challenging in SQL.
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