Complex SQL Query with JSON Aggregation

SELECT
	SubQ.*,
	(
		SELECT
			array_to_json(ARRAY (
					SELECT
						json_build_object(child_r.role_code, count(child_d.id))
					FROM distributors child_d, users child_u, roles_users child_ru, roles child_r
					WHERE
						child_d.personal_sponsor_distributor_id BETWEEN SubQ.distributor_id / 100 * 100 AND SubQ.distributor_id / 100 * 100 + 99
						AND child_u.id = child_d.user_id
						AND child_ru.user_id = child_u.id
						AND child_r.id = child_ru.role_id
						AND child_u.status_id NOT IN (2, 3, 6, 8)
						AND child_d.is_master IS TRUE GROUP BY child_r.role_code))) count_personal_sponsored
FROM (
	SELECT
		ui.distributor_id,
		da.external_distributor_id,
		ui.user_id,
		ui.login,
		ui.email,
		ui.status_id,
		ui.entry_date,
		ui.role_start_date,
		ui.next_renewal_date,
		ui.service_renewal_date,
		s.name AS status_name,
		ui.phone,
		cc.code country_code,
		ui.firstname,
		ui.lastname,
		ui.role_code,
		ui.role_name,
		ui.is_guest,
		ui.personal_sponsor_distributor_id AS u_sponsor_id,
		ui.dualteam_sponsor_distributor_id AS d_sponsor_id,
		ui.dualteam_current_position AS position,
		CASE WHEN ui.status_id NOT IN (2, 3, 6, 8)
			AND ui.next_renewal_date > now() THEN
			TRUE
		ELSE
			FALSE
		END AS active,
		CASE WHEN ui.status_id NOT IN (2, 3, 6, 8)
			AND ui.service_renewal_date > now() THEN
			TRUE
		ELSE
			FALSE
		END AS service_active,
		sp.firstname AS sponsor_firstname,
		sp.lastname AS sponsor_lastname,
		sp.phone AS sponsor_phone,
		sp.email AS sponsor_email,
		ui.lifetime_rank AS rank_id,
		cr.name AS rank_name,
		cr_ass.id AS rank_image_id,
		cr_ass.attachment_file_name AS rank_image_name,
		cr_ass.type AS rank_image_type,
		(
			SELECT
				ut.created_at
			FROM
				user_tracks ut
			WHERE
				ut.user_id = ui.user_id
			ORDER BY
				id DESC
			LIMIT 1) last_login,
		detail.pqv,
		detail.gqv,
		detail.additional_info,
		detail.active AS commission_active,
		pr_cr.rank_identity AS curr_rank_id,
		pr_cr.name AS curr_rank_name,
		pr_ass.id AS curr_rank_image_id,
		pr_ass.attachment_file_name AS curr_rank_image_name,
		pr_ass.type AS curr_rank_image_type
	FROM
		user_infos ui
		INNER JOIN statuses s ON ui.status_id = s.id
		LEFT JOIN bonus.bonusm20240501_details_unilevel detail ON detail.distributor_id = ui.distributor_id
		LEFT JOIN bonus.bonusm20240501_ranks rank ON rank.distributor_id = ui.distributor_id
		LEFT JOIN client_ranks pr_cr ON pr_cr.rank_identity = rank.paid_rank
			AND pr_cr.rank_identity >= 40
	LEFT JOIN assets pr_ass ON pr_ass.viewable_id = pr_cr.id
		AND pr_ass.viewable_type = 'ClientRank'
	LEFT JOIN user_infos sp ON ui.personal_sponsor_distributor_id = sp.distributor_id
	LEFT JOIN client_ranks cr ON cr.rank_identity = ui.lifetime_rank
		AND cr.rank_identity >= 40
	LEFT JOIN assets cr_ass ON cr_ass.viewable_id = cr.id
		AND cr_ass.viewable_type = 'ClientRank'
	LEFT JOIN country_codes cc ON cc.country_iso = ui.country_iso
	LEFT JOIN distributor_addons da ON ui.distributor_id = da.distributor_id
WHERE
	ui.distributor_id = 10401901
	AND ui.status_id NOT IN (2, 3, 6, 8)) SubQ;