SELECT 
    reports.id AS id,
    reports.date AS date,
    reports.trafficchannelid AS trafficchannelid,
    reports.final AS final,
    reports.compressedreport AS compressedreport,
    reports.created_at AS created_at,
    reports.updated_at AS updated_at,
    JSON_AGG(
        JSON_BUILD_OBJECT(
           'campaignid', campaignid,
           'funneltemplateid', funneltemplateid,
           'campaignidrt', campaignidrt,
           'adGroups', (
                SELECT JSON_AGG(
                    JSON_BUILD_OBJECT(
                       'adgroupid', report_adgroups.adgroupid,
                       'ad', (
                            SELECT JSON_BUILD_OBJECT(
                               'adid', report_ads.adid,
                               'adname', report_ads.adname,
                               'adgroupname', report_ads.adgroupname,
                               'campaignname', report_ads.campaignname,
                               'date', report_ads.date,
                               'total_spent', report_ads.total_spent,
                               'total_click', report_ads.total_click,
                               'total_impressions', report_ads.total_impressions,
                               'revenue', report_ads.revenue,
                               'estimate', report_ads.estimate,
                               'feedsource', report_ads.feedsource,
                               'clickdetail', (
                                    SELECT JSON_Build_OBJECT(
                                       'track_id', report_ads_clickdetail.track_id,
                                       'domain_id', report_ads_clickdetail.domain_id
                                    )
                                    FROM report_ads_clickdetail
                                    WHERE report_ads_clickdetail.report_ads_id = report_ads.id
						    		LIMIT 1
                                )
                            )
                            FROM report_ads
                            WHERE report_ads.report_adgroups_id = report_adgroups.id
						    LIMIT 1
                        )
                    )
                )
                FROM report_adgroups
                WHERE report_adgroups.report_details_id = report_details.id
				LIMIT 1
            )
        )
    ) AS details,
    JSON_AGG(
        JSON_BUILD_OBJECT(
           'campaigns', report_domains.campaigns,
           'domainid', report_domains.domainid,
           'name', report_domains.name,
           'weight', report_domains.weight,
           'feedsource', report_domains.feedsource
        )
    ) AS domains
FROM reports
LEFT JOIN report_details on  report_details.report_id = reports.id 
LEFT JOIN report_adgroups on report_adgroups.report_details_id = report_details.id
LEFT JOIN report_ads on report_ads.report_adgroups_id = report_adgroups.id
LEFT JOIN report_ads_clickdetail on report_ads_clickdetail.report_ads_id = report_ads.id
LEFT JOIN report_conversion on report_conversion.report_ads_clickdetail_id = report_ads_clickdetail.id
LEFT JOIN report_domains on  report_domains.reportid = reports.id 
WHERE reports.date = CAST('2023-09-21' AS DATE) AND trafficchannelid='1985930498438577' AND reports.final = true
AND reports.created_at = (
    SELECT MAX(reports.created_at)
    FROM reports
	WHERE reports.date = reports.date
)
GROUP BY reports.id, reports.date, reports.trafficchannelid;

This query is supposed to return from reports. It returns the result but it only returns reports with the latest reports.created_at.    The issue is before the subqery after AND, the result returned 4 rows. But now it returns 0. How do I fix the issue?