I want to group adgroups by adgroupid in the below query SELECT reports.date AS date,…
- User
I want to group adgroups by adgroupid in the below query
SELECT reports.date AS date, reports.trafficchannelid AS trafficchannelid, reports.final AS final, reports.compressedreport AS compressedreport, 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_AGG( 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 and report_ads_clickdetail.report_id = reports.id GROUP BY report_adgroups.adgroupid ) ) FROM report_ads WHERE report_ads.report_adgroups_id = report_adgroups.id ) ) ) FROM report_adgroups WHERE report_adgroups.report_details_id = report_details.id GROUP BY report_details.campaignid,report_details.funneltemplateid,report_details.campaignidrt ) ) ) AS details 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 WHERE reports.trafficchannelid = '1148711119076948' AND reports.date = '2023-09-21' GROUP BY reports.trafficchannelid, reports.final, reports.compressedreport, reports.date
- Assistant
To group adgroups by adgroupid in the query, you can modify the subquery that fetches the adgroups:
SELECT reports.date AS date, reports.trafficchannelid AS trafficchannelid, reports.final AS final, reports.compressedreport AS compressedreport, JSON_AGG( JSON_BUILD_OBJECT( 'campaignid', campaignid, 'funneltemplateid', funneltemplateid, 'campaignidrt', campaignidrt, 'adGroups', ( SELECT JSON_AGG( JSON_BUILD_OBJECT( 'adgroupid', report_adgroups.adgroupid, 'ads', ( SELECT JSON_AGG( 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_AGG( 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 and report_ads_clickdetail.report_id = reports.id GROUP BY report_ads.adid ) ) FROM report_ads WHERE report_ads.report_adgroups_id = report_adgroups.id ) ) ) ) FROM report_adgroups WHERE report_adgroups.report_details_id = report_details.id GROUP BY report_adgroups.adgroupid ) ) ) AS details 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 WHERE reports.trafficchannelid = '1148711119076948' AND reports.date = '2023-09-21' GROUP BY reports.trafficchannelid, reports.final, reports.compressedreport, reports.dateIn this modified query, I have changed the grouping of the adGroups subquery to
GROUP BY report_adgroups.adgroupid. This will group the adgroups by their adgroupid. - User
The problem I am facing with the query is grouping issue. A row is repeated numerous times and is not grouped by. Can you please add group by option in the necessary areas and places?
- Assistant
Apologies for the confusion. Here's an updated version of the query with the necessary groupings:
SELECT reports.date AS date, reports.trafficchannelid AS trafficchannelid, reports.final AS final, reports.compressedreport AS compressedreport, JSON_AGG( JSON_BUILD_OBJECT( 'campaignid', campaignid, 'funneltemplateid', funneltemplateid, 'campaignidrt', campaignidrt, 'adGroups', ( SELECT JSON_AGG( JSON_BUILD_OBJECT( 'adgroupid', report_adgroups.adgroupid, 'ads', ( SELECT JSON_AGG( 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_AGG( 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 and report_ads_clickdetail.report_id = reports.id ) ) FROM report_ads WHERE report_ads.report_adgroups_id = report_adgroups.id GROUP BY report_ads.adid -- Group by adid ) ) ) FROM report_adgroups WHERE report_adgroups.report_details_id = report_details.id GROUP BY report_adgroups.adgroupid -- Group by adgroupid ) ) ) ) AS details 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 WHERE reports.trafficchannelid = '1148711119076948' AND reports.date = '2023-09-21' GROUP BY reports.date, reports.trafficchannelid, reports.final, reports.compressedreportIn this updated query, I have added the
GROUP BYclauses forreport_ads.adidto group the ads within each ad group andreport_adgroups.adgroupidto group the ad groups within each campaign. Additionally, I have also added thereports.dateto theGROUP BYclause to ensure proper grouping. - User
How to choose only the last insertion on the table reports? I have a created_at and updated_at field
- Assistant
To select only the last insertion on the
reportstable based on thecreated_atorupdated_atfields, you can use theORDER BYclause with the corresponding field in descending order and limit the result to one row. Here's an example:SELECT reports.date AS date, reports.trafficchannelid AS trafficchannelid, reports.final AS final, reports.compressedreport AS compressedreport, JSON_AGG( JSON_BUILD_OBJECT( 'campaignid', campaignid, 'funneltemplateid', funneltemplateid, 'campaignidrt', campaignidrt, 'adGroups', ( SELECT JSON_AGG( JSON_BUILD_OBJECT( 'adgroupid', report_adgroups.adgroupid, 'ads', ( SELECT JSON_AGG( 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_AGG( 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 and report_ads_clickdetail.report_id = reports.id ) ) FROM report_ads WHERE report_ads.report_adgroups_id = report_adgroups.id GROUP BY report_ads.adid ORDER BY report_ads.created_at DESC -- or use report_ads.updated_at if preferred LIMIT 1 ) ) ) FROM report_adgroups WHERE report_adgroups.report_details_id = report_details.id GROUP BY report_adgroups.adgroupid ) ) ) ) AS details 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 WHERE reports.trafficchannelid = '1148711119076948' AND reports.date = '2023-09-21' GROUP BY reports.date, reports.trafficchannelid, reports.final, reports.compressedreport ORDER BY reports.created_at DESC -- or use reports.updated_at if preferred LIMIT 1In this updated query, I added the
ORDER BY reports.created_at DESC(orreports.updated_at DESC) clause at the end to sort the rows in descending order based on thecreated_at(orupdated_at) field. Then, I included theLIMIT 1clause to only retrieve the last row based on the sorting. This will ensure that you get only the latest insertion in thereportstable. - User
It says it is not part of the group by. How can I use it without adding in the group by?
- Assistant
Apologies for the confusion. If you want to select the last insertion from the
reportstable without including thereports.created_at(orreports.updated_at) field in theGROUP BYclause, you can use a subquery to filter the results based on the latestcreated_at(orupdated_at) value. Here's an updated version of the query:SELECT reports.date AS date, reports.trafficchannelid AS trafficchannelid, reports.final AS final, reports.compressedreport AS compressedreport, JSON_AGG( JSON_BUILD_OBJECT( 'campaignid', campaignid, 'funneltemplateid', funneltemplateid, 'campaignidrt', campaignidrt, 'adGroups', ( SELECT JSON_AGG( JSON_BUILD_OBJECT( 'adgroupid', report_adgroups.adgroupid, 'ads', ( SELECT JSON_AGG( 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_AGG( 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 and report_ads_clickdetail.report_id = reports.id ) ) FROM report_ads WHERE report_ads.report_adgroups_id = report_adgroups.id GROUP BY report_ads.adid ) ) ) FROM report_adgroups WHERE report_adgroups.report_details_id = report_details.id GROUP BY report_adgroups.adgroupid ) ) ) ) AS details 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 WHERE reports.trafficchannelid = '1148711119076948' AND reports.date = '2023-09-21' AND reports.created_at = (SELECT MAX(created_at) FROM reports) -- Filtering based on the latest created_at value GROUP BY reports.date, reports.trafficchannelid, reports.final, reports.compressedreportIn this modified query, I added an additional condition in the
WHEREclause to filter the results based on the latestcreated_atvalue from thereportstable. By using(SELECT MAX(created_at) FROM reports), we are comparing thecreated_atvalue of each row with the maximum value present in the entire table. This will select only the row(s) with the latestcreated_atvalue without including it in theGROUP BYclause. - User
Now I have another issue. If for example clickdetail has 28 items in its array, item inside details will be repeated 28 times too. but the items should only be shown once and shouldnt be repeated depending on the click details
- Assistant
To avoid repeating the items based on the number of click details in the
clickdetailarray, you can move the subquery forclickdetailoutside theJSON_BUILD_OBJECTand cross join it with thereport_adstable usingLEFT JOIN LATERAL. This will ensure that each row in thereport_adstable is matched with the corresponding click details. Here's the updated query:SELECT reports.date, reports.trafficchannelid, reports.final, reports.compressedreport, JSON_AGG( JSON_BUILD_OBJECT( 'campaignid', campaignid, 'funneltemplateid', funneltemplateid, 'campaignidrt', campaignidrt, 'adGroups', ( SELECT JSON_AGG( JSON_BUILD_OBJECT( 'adgroupid', report_adgroups.adgroupid, 'ads', ( SELECT JSON_AGG( 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 ) ) FROM report_ads WHERE report_ads.report_adgroups_id = report_adgroups.id GROUP BY report_ads.adid, report_ads.adname, report_ads.adgroupname, report_ads.campaignname, report_ads.date, report_ads.total_spent, report_ads.total_click, report_ads.total_impressions, report_ads.revenue, report_ads.estimate, report_ads.feedsource ) ) FROM report_adgroups WHERE report_adgroups.report_details_id = report_details.id GROUP BY report_adgroups.adgroupid ) ) ) AS details 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 LATERAL ( SELECT JSON_AGG( JSON_BUILD_OBJECT( 'track_id', report_ads_clickdetail.track_id, 'domain_id', report_ads_clickdetail.domain_id ) ) AS clickdetail FROM report_ads_clickdetail WHERE report_ads_clickdetail.report_ads_id = report_ads.id AND report_ads_clickdetail.report_id = reports.id ) AS clickdetail ON true WHERE reports.trafficchannelid = '1148711119076948' AND reports.date = '2023-09-21' GROUP BY reports.date, reports.trafficchannelid, reports.final, reports.compressedreportIn this updated query, the subquery for
clickdetailis moved outside theJSON_BUILD_OBJECT. It is transformed into a lateral join usingLEFT JOIN LATERAL. This allows each row inreport_adsto be matched with the corresponding click details. The click details are aggregated separately usingJSON_AGGin the lateral subquery. This ensures that the items insidedetailsare not repeated for each click detail.Make sure to adjust the grouping in the subquery for
report_adsbased on the necessary fields to avoid duplicate items. - User
ERROR: syntax error at or near "FROM" LINE 46: FROM report_adgroups
- Assistant
Apologies for the error in the query. It seems there was a syntax issue. Please try the updated query below:
SELECT reports.date, reports.trafficchannelid, reports.final, reports.compressedreport, JSON_AGG( JSON_BUILD_OBJECT