SELECT
	m.id,
	m.name,

	-- Since we performed a GROUP BY on the outer table, our LATER JOIN derived table is
	-- now available for aggregation.
	COUNT( * ) AS commentCount,
	-- Using the JSON functions (MySQL 5.7.22+), we can collapse the "TOP N" rows for
	-- each outer row into a JSON payload (array of objects).
	JSON_ARRAYAGG(
		-- Applied to each row in the LATERAL derived table.
		JSON_OBJECT(
			'id', recent.id,
			'createdAt', recent.createdAt
		)
	) AS comments
FROM
	member m

-- By using a LATERAL JOIN (MySQL 8+), we can create a derived table PER EACH ROW of the
-- outer table. This per-row derived table is made available within the SELECT clause.
INNER JOIN LATERAL
	(

		-- Since this lateral join derived table is being calculated per row, we can
		-- easily grab the "TOP N" corresponding records (using LIMIT) for each row in the
		-- outer table.
		SELECT
			c.id,
			c.createdAt
		FROM
			blog_comment c
		WHERE
			c.memberID = m.id -- Per row join condition.
		ORDER BY
			c.id DESC
		LIMIT
			5

	) AS recent

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