Utilizing LATERAL Joins To Get “Prime N” Information From Every Group In MySQL 8.0.14

One sort of SQL question that has been significantly exhausting, traditionally, is getting the “Prime N” rows for every group in a MySQL aggregation. When MySQL added LATERAL joins in 8.0.14, nonetheless, they opened the door for some easy however fairly highly effective question methods. A number of months in the past, I checked out utilizing LATERAL joins to collect row-specific aggregates. In that submit, I used conventional mixture capabilities like COUNT() and MAX(). However, as I just lately demonstrated, MySQL’s JSON aggregates (5.7.22+) may be nested for some fairly thrilling outcomes. On this submit, I wish to have a look at combining LATERAL joins with JSON aggregates to learn the “Prime N” rows from a GROUP BY question.

To display this, within the context of this weblog, I’ll craft a SQL question that will get the oldest 10 members; and, for every member, I wish to get the latest 5 feedback. Getting the oldest 10 members is straightforward and has been potential in SQL because the daybreak of time:

SELECT
	m.id,
	m.title
FROM
	member m
WHERE
	m.id <= 10

On this case, I am leaning on the truth that my id is an AUTO_INCREMENT column. Which suggests, the primary 10 id values within the desk characterize the oldest members within the desk.

ASIDE: In a easy question like this, I might have executed ORDER BY m.id DSEC LIMIT 10 to really get the oldest 10 members. However, utilizing this method blows up while you begin becoming a member of to different tables.

Now, I’ll add a LATERAL derived be part of desk that can get the latest 5 feedback for every member. Because the LATERAL sub-query is executed for every row of the outer desk, it signifies that our LATERAL SQL can reference columns within the outer row. On this case, we will match on m.id:

SELECT
	m.id,
	m.title
FROM
	member m

-- By utilizing a LATERAL JOIN (MySQL 8+), we are able to create a derived desk PER EACH ROW of the
-- outer desk. This per-row derived desk is made obtainable inside the SELECT clause.
INNER JOIN LATERAL
	(

		-- Since this lateral be part of derived desk is being calculated per row, we are able to
		-- simply seize the "TOP N" corresponding data (utilizing LIMIT) for every row within the
		-- outer desk.
		SELECT
			c.id,
			c.createdAt
		FROM
			blog_comment c
		WHERE
			c.memberID = m.id -- Per-row be part of situation.
		ORDER BY
			c.id DESC
		LIMIT
			5

	) AS latest

WHERE
	m.id <= 10

As you’ll be able to see right here, the LATERAL be part of permits us to make use of a per-row be part of situation:

WHERE c.memberID = m.id

… which, in flip, permits us to collect distinctive blog_comment data for every of the member data.

In fact, this INNER JOIN LATERAL nonetheless works like another INNER JOIN which signifies that the results of this SQL question is a cross product of the 2 tables. This offers us far more rows that we needed:

As you’ll be able to see, we find yourself with “Ben Nadel” 5 instances on this resultset since we get the cross product of every member row and the (at most) 5 feedback that every member has left on the weblog.

To repair this, we are able to now GROUP BY the outer row. In some SQL engines, your GROUP BY clause has to comprise all of the columns that you just wish to group. Nonetheless, one of many very good issues within the MySQL-specific syntax is that you just solely want to incorporate simply one of many columns and MySQL figures it out. On this case, we we’re going to group by the member id:

SELECT
	m.id,
	m.title
FROM
	member m

-- By utilizing a LATERAL JOIN (MySQL 8+), we are able to create a derived desk PER EACH ROW of the
-- outer desk. This per-row derived desk is made obtainable inside the SELECT clause.
INNER JOIN LATERAL
	(

		-- Since this lateral be part of derived desk is being calculated per row, we are able to
		-- simply seize the "TOP N" corresponding data (utilizing LIMIT) for every row within the
		-- outer desk.
		SELECT
			c.id,
			c.createdAt
		FROM
			blog_comment c
		WHERE
			c.memberID = m.id -- Per-row be part of situation.
		ORDER BY
			c.id DESC
		LIMIT
			5

	) AS latest

WHERE
	m.id <= 10

-- To cut back the cross product of the member desk and the blog_comment desk, we are able to
-- group the outcomes by the member.
GROUP BY
	m.id

Now, once we run this, with the GROUP BY m.id, we get the next MySQL outcomes:

As you’ll be able to see, we’re again right down to a single report per member. The “Prime N” feedback that we generated in our LATERAL be part of have been collapsed. In an effort to extract the remark info, we will use MySQL’s JSON mixture capabilities. The JSON_ARRAYAGG() operate permits us to compose grouped expressions right into a JSON array. And, the JSON_OBJECT() operate permits us to mixture row columns because the expression being composed into the JSON array:

JSON_ARRAYAGG(
	-- Utilized to every row within the LATERAL derived desk.
	JSON_OBJECT(
		'id', latest.id,
		'createdAt', latest.createdAt
	)
) AS feedback

Right here, the JSON_OBJECT() operate is being utilized to every row within the grouping. That means, we’re every blog_comment row within the LATERAL derived desk and we’re setting up a JSON object. Then, the JSON_ARRAYAGG() operate takes these JSON objects and composes them right into a single array per member report.

The entire question appears like this:

SELECT
	m.id,
	m.title,

	-- Since we carried out a GROUP BY on the outer desk, our LATER JOIN derived desk is
	-- now obtainable for aggregation.
	COUNT( * ) AS commentCount,
	-- Utilizing the JSON capabilities (MySQL 5.7.22+), we are able to collapse the "TOP N" rows for
	-- every outer row right into a JSON payload (array of objects).
	JSON_ARRAYAGG(
		-- Utilized to every row within the LATERAL derived desk.
		JSON_OBJECT(
			'id', latest.id,
			'createdAt', latest.createdAt
		)
	) AS feedback
FROM
	member m

-- By utilizing a LATERAL JOIN (MySQL 8+), we are able to create a derived desk PER EACH ROW of the
-- outer desk. This per-row derived desk is made obtainable inside the SELECT clause.
INNER JOIN LATERAL
	(

		-- Since this lateral be part of derived desk is being calculated per row, we are able to
		-- simply seize the "TOP N" corresponding data (utilizing LIMIT) for every row within the
		-- outer desk.
		SELECT
			c.id,
			c.createdAt
		FROM
			blog_comment c
		WHERE
			c.memberID = m.id -- Per row be part of situation.
		ORDER BY
			c.id DESC
		LIMIT
			5

	) AS latest

WHERE
	m.id <= 10
GROUP BY
	m.id
;

And, once we run this in MySQL 8.0.14+, we get the next output:

As you’ll be able to see, the JSON_ARRAYAGG() and JSON_OBJECT() capabilities have taken the collapsed / grouped LATERAL be part of data and uncovered them as a JSON payload within the MySQL recordset. This has allowed us to get the “Prime N” feedback for every member within the group. In fact, in your software code you will should deserialize the JSON payload to get the feedback as an array; nonetheless, that must be a functionality natively constructed into your software runtime.

Is not SQL simply thrilling! Once in a while, I am going to hear somebody diminish SQL as one thing that folks “have” to make use of. Neglect that! I love SQL. And, I really like that the SQL engines are continually including new and groovy performance.

Efficiency Concerns

Need to use code from this submit?
Try the license.