PostgreSQL’s two popular functions JSON object builder and JSON aggregator are good when you want to fetch a list of child data along with the parent and pass that child data with the parent to the calling service.

I used a similar approach while fetching selected column data from the child table. Here is the native query I’ve written along with the repository method.

@Query(value = """
            SELECT
                json_build_object(
                    'id', jobCard.id,
                    'vehicleId', vehicle1.id,
                    'license', vehicle1.license_no,
                    'generatedId', jobCard.generated_id,
                    'engine', vehicle1.engine_no,
                    'chassisNo', vehicle1.chassis_no,
                    'model', vehicle1.model,
                    'yearModel', vehicle1.year_model,
                    'spareParts', jobCardParts.spareParts,
                    'jobServices', jobCardService.jobServices,
                    'totalPartsCost', jobCardParts.totalPartsCost,
                    'totalServiceCost', jobCardService.totalServiceCost,
                    'complains', jobCardComplain.complains,
                    'preparedById', preparedBy.id,
                    'preparedByName', preparedBy.name,
                    'assignedWithId', assignedWith.id,
                    'assignedWithName', assignedWith.name,
                    'handoverId', handoverMTO.id,
                    'handoverName', handoverMTO.name,
                    'takeoverId', takeoverMTo.id,
                    'takeoverName', takeoverMTo.name,
                    'status', CASE
                        WHEN jobCard.status = 0 THEN 'New'
                        WHEN jobCard.status = 1 THEN 'On going'
                        WHEN jobCard.status = 2 THEN 'Completed'
                        WHEN jobCard.status = 3 THEN 'Closed'
                    END,
                    'issueDate', jobCard.issuing_date,
                    'handoverDate', jobCard.handover_date
                ) AS job_details
            FROM
                job_card jobCard
            LEFT JOIN
                vehicle vehicle1 ON vehicle1.id = jobCard.vehicle_id
            LEFT JOIN
                technicians assignedWith ON assignedWith.id = jobCard.assigned_with
            LEFT JOIN
                workshops workshop ON workshop.id = jobCard.workshop_id
            LEFT JOIN
                driver handoverMTO ON handoverMTO.id = jobCard.handover_id
            LEFT JOIN
                driver takeoverMTo ON takeoverMTo.id = jobCard.takeover_id
            LEFT JOIN
                employee preparedBy ON preparedBy.id = jobCard.prepared_by_id
            LEFT JOIN (
                SELECT
                    job_card_id,
                    json_agg(
                        json_build_object(
                            'jobCardPartsId', jobParts.id,
                            'partsId', jobParts.parts_id,
                            'partsName', part.name,
                            'unitId', jobParts.unit_id,
                            'unitName', partsUnit.full_name,
                            'partsQuantity', jobParts.quantity,
                            'partsCost', jobParts.cost
                        )
                    ) AS spareParts,
                    COALESCE(SUM(CASE WHEN jobParts.is_active = true THEN jobParts.cost ELSE 0 END), 0)
                        AS totalPartsCost
                FROM
                    job_parts jobParts
                JOIN
                    parts part ON part.id = jobParts.parts_id
                JOIN
                    unit partsUnit ON partsUnit.id = jobParts.unit_id
                WHERE
                    jobParts.is_active = true
                GROUP BY
                    job_card_id
            ) AS jobCardParts ON jobCardParts.job_card_id = jobCard.id
            LEFT JOIN (
                SELECT
                    job_card_id,
                    json_agg(
                        json_build_object(
                            'serviceId', jobService.id,
                            'serviceTitle', jobService.title,
                            'serviceCost', jobService.cost
                        )
                    ) AS jobServices,
                    COALESCE(SUM(CASE WHEN jobService.is_active = true THEN jobService.cost ELSE 0 END), 0)
                        AS totalServiceCost
                FROM
                    job_service jobService
                WHERE
                    jobService.is_active = true
                GROUP BY
                    job_card_id
            ) AS jobCardService ON jobCardService.job_card_id = jobCard.id
            LEFT JOIN (
                SELECT
                    job_card_id,
                    json_agg(complains) AS complains
                FROM (
                    SELECT
                        job_card_id,
                        complain.complains
                    FROM
                        job_card_complains complain
                ) AS subquery
                GROUP BY
                    job_card_id
            ) AS jobCardComplain ON jobCardComplain.job_card_id = jobCard.id
            WHERE
                jobCard.is_active = true
                AND jobCard.id = :id""", nativeQuery = true)
    String findJobCardById(@Param("id") Long id);

Later, I used an instance of object mapper to map the result to the specified DTO I prepared in the service layer.

public JobCardProjection getJobCardById(Long id) {
        objectMapper.configure(DeserializationFeature.FAIL_ON_UNKNOWN_PROPERTIES, false);
        String jsonResult = jobCardRepository.findJobCardById(id);
        try {
            JobCardProjection jobCard = objectMapper.readValue(jsonResult, JobCardProjection.class);
            return jobCard;
        } catch (JsonProcessingException e) {
            throw new RuntimeException(e);
        }
    }

For clarification, I am sharing the contents of the DTO record JobCardProjection.

public record JobCardProjection(
        Long id,
        Long vehicleId,
        String license,
        String generatedId,
        String engine,
        String chassisNo,
        String model,
        String yearModel,
        Set<JobPartsProjection> spareParts,
        Set<JobServiceProjection> jobServices,
        Double totalPartsCost,
        Double totalServiceCost,
        Set<String> complains,
        Long preparedById,
        String preparedByName,
        Long assignedWithId,
        String assignedWithName,
        Long handoverId,
        String handoverName,
        Long takeoverId,
        String takeoverName,
        String status,
        LocalDateTime issueDate,
        LocalDateTime handoverDate
) {}

Although it reduces additional overhead, this approach is no longer database agnostic. Given that there is a db migration in the future, we have to touch the repository code and are required to find equivalent functions for JSON object builder as well as JSON aggregator.

My query is if there is a JPQL alternative for the native query I’ve prepared.