query.sql

SELECT 
		dist.name,
		dist_info.title,
		dist_info.short_note,
        imgs.images,
        ara.areas,
        plc.places,
        trans.transports
	FROM 
		gg_districts dist
	JOIN
		gg_districts_info dist_info 
            ON dist_info.dist_id = dist.id
    JOIN LATERAL (
		SELECT json_agg(
			json_build_object(
				'image', dt_imgs.image,
				'altText', dt_imgs.image_alt
			)
		) AS images
		FROM 
			gg_districts_imgs AS dt_imgs
		WHERE 
			dt_imgs.dist_id = dist.id
        FETCH FIRST 15 ROW ONLY
	) imgs on TRUE
	LEFT JOIN LATERAL (
		SELECT json_agg(
			json_build_object(
                'name', area.name,
                'slug', area.slug,
                'image', area.card_img
            )
		) AS areas
        FROM 
            gg_areas AS area
        WHERE 
            area.dist_id = dist.id 
            AND area.is_active = TRUE
	) ara ON TRUE
    LEFT JOIN LATERAL(
        SELECT json_agg(
            json_build_object(
                'name', dist_plc.name,
                'slug', dist_plc.slug,
                'image', dist_plc.card_img,
				'altText', dist_plc.card_img_alt
            )
        ) AS places
        FROM 
            gg_places as dist_plc
        LEFT JOIN 
            gg_areas AS ar
                ON ar.dist_id = dist_plc.dist_id
                AND ar.id = dist_plc.area_id
        WHERE
            dist_plc.is_districttop = TRUE
            AND dist_plc.dist_id = dist.id
    ) plc ON TRUE
	LEFT JOIN LATERAL (
		SELECT json_agg(
			json_build_object(
                'name', ts.name,
                'shortName', ts.short_name
            )
		) AS transports
        FROM 
            transit_systems_dist AS dist_trans
		JOIN 
            transit_systems ts 
                ON ts.id = dist_trans.transit_id
        WHERE 
            dist_trans.dist_id = dist.id
	) trans ON true
	WHERE 
		dist.slug = 'hapur'
		AND dist.is_active = TRUE;