CODEWITHSUNDEEP
javamongodbspring-data-mongodbmongotemplate
Amir Azizkhani
Amir Azizkhani

Reputation: 1813

Spring MongoDB : Aggregation first record base on date

in my app, customers Are evaluated weekly and i need latest their evaluations in specific branch codes and their category.

in my json data:

branch code: customerView.branchCode

category : cardInfo._id

evaluation date: date

Object (Json):

/* 1 */
{
    "_id" : ObjectId("5d2c552443a99e1c2464b23a"),
    "customerView" : {
        "_id" : NumberLong(3278642838),
        "number" : NumberLong(2374051),
        "type" : 1,
        "branchCode" : "1100"
    },
    "cardInfo" : {
        "_id" : ObjectId("5cee1eaa9eae1e1330b5219c"),
        "modelId" : "5cecbbcd9eae1e0be0530ccb",

    },
    "totalScore" : 21.0,
    "date" : ISODate("2019-07-14T19:30:00.000Z"),
    "version" : 0,
    "_class" : "entity.CustomerAssessmentDocument"
}
,
/* 2 */
{
    "_id" : ObjectId("5d2d57da43a99e1a5c4728d7"),
    "customerView" : {
        "_id" : NumberLong(3278642838),
        "number" : NumberLong(2374051),
        "type" : 1,
        "branchCode" : "1100"
    },
    "cardInfo" : {
        "_id" : ObjectId("5cee1eaa9eae1e1330b5219c"),
        "modelId" : "5cecbbcd9eae1e0be0530ccb"

    },
    "totalScore" : 11.0,
    "date" : ISODate("2019-07-15T19:30:00.000Z"),
    "version" : 0,
    "_class" : "entity.CustomerAssessmentDocument"
}

in this sample i have two record with same customerView.number and cardInfo._id and i need object with 5d2d57da43a99e1a5c4728d7 id, because that's date is greater than that one.

Java:

public List<CustomerAssessmentDocument.CustomerAssessmentCustomerNumberProjection> getTopCustomersSortedByDateInBranch(
            String cardId, Set<String> branches) {

        final Criteria criteria =
                Criteria.where("totalScore").ne(0D)
                        .and("customerView.branchCode").in(branches)
                        .and("cardInfo._id").is(new ObjectId(cardId));

        Aggregation aggregation = Aggregation.newAggregation(
                Aggregation.sort(Sort.Direction.DESC, "date"),
                Aggregation.match(criteria),
                Aggregation.project("totalScore", "customerView", "date"),
                Aggregation.group("$customerView.number").first("date").as("date")
        );

        final AggregationResults<CustomerAssessmentDocument.CustomerAssessmentCustomerNumberProjection> customerAssessmentDocument =
                mongoTemplate.aggregate(
                        aggregation,
                        "CS_ASSESSMENT",
                        CustomerAssessmentDocument.CustomerAssessmentCustomerNumberProjection.class
                );
        return customerAssessmentDocument.getMappedResults();
    }

Java Classes:

@Document(collection = "CS_ASSESSMENT")
public class CustomerAssessmentDocument extends AuditDocument {
    @Id
    private String id;
    private CustomerViewEnt customerView;
    private CardInfo cardInfo;
    private Double totalScore;
    private String description;
    private String assessmentReqId;
    private Date date;
    private Date effectiveDate;
...
    public class CustomerAssessmentCustomerNumberProjection {
        /**
         * CustomerAssessmentDocument->customerView.number
         */
        private Long id;
        /**
         * CustomerAssessmentDocument->date
         */
        private Date date;
        /**
         * CustomerAssessmentDocument->totalScore
         */
        private Double totalScore;
    }
}

return object :

0 =>
     id =  2374051 /*customerView.number*/
     date =  "2019-07-15T19:30:00.000Z"
     totalScore = null

anyone can help me how can get totalScore value inside customer number?

Upvotes: 1

Views: 2164

Answers (1)

Amir Azizkhani
Amir Azizkhani

Reputation: 1813

in group aggregation, as() return chain GroupOption Object that it can complete other fields base on first(...) or last(...) record.

Aggregation.group("$customerView.number").first("totalScore").as("totalScore").first("date").as("date")

return object:

0 =>
    id =  2374051 /*customerView.number*/
    date =  "2019-07-15T19:30:00.000Z"
    totalScore = 11.0

Upvotes: 1

Related Questions