Spring data MongoDb query based on last element of nested array field

Question

I have the following data (Cars):

[
    {
        "make" : “Ferrari”,
        "model" : “F40",
        "services" : [ 
            {
                "type" : "FULL",
                “date_time" : ISODate("2019-10-31T09:00:00.000Z"),
            }, 
            {
                "type" : "FULL",
                "scheduled_date_time" : ISODate("2019-11-04T09:00:00.000Z"),
            }
        ],
    },
    {
        "make" : "BMW",
        "model" : “M3",
        "services" : [ 
            {
                "type" : "FULL",            
                "scheduled_date_time" : ISODate("2019-10-31T09:00:00.000Z"),
            }, 
            {
                "type" : "FULL",             
                “scheduled_date_time" : ISODate("2019-11-04T09:00:00.000Z"),
            }
        ],
    }
]

Using Spring data MongoDb I would like a query to retrieve all the Cars where the scheduled_date_time of the last item in the services array is in-between a certain date range.

A query which I used previously when using the first item in the services array is like:

mongoTemplate.find(Query.query(
        where("services.0.scheduled_date_time").gte(fromDate)
            .andOperator(
                where("services.0.scheduled_date_time").lt(toDate))),
        Car.class);

Note the 0 index since it's first one as opposed to the last one (for my current requirement).

I thought using an aggregate along with a projection and .arrayElementAt(-1) would do the trick but I haven't quite got it to work. My current effort is:

Aggregation agg = newAggregation(
        project().and("services").arrayElementAt(-1).as("currentService"),
        match(where("currentService.scheduled_date_time").gte(fromDate)
            .andOperator(where("currentService.scheduled_date_time").lt(toDate)))
    );

    AggregationResults<Car> results = mongoTemplate.aggregate(agg, Car.class, Car.class);

    return results.getMappedResults();

Any help suggestions appreciated.

Thanks,

Answer 1

This mongo aggregation retrieves all the Cars where the scheduled_date_time of the last item in the services array is in-between a specific date range.

 [{
        $addFields: {
            last: {
                $arrayElemAt: [
                    '$services',
                    -1
                ]
            }
        }
    }, {
        $match: {
            'last.scheduled_date_time': {
                $gte: ISODate('2019-10-26T04:06:27.307Z'),
                $lt: ISODate('2019-12-15T04:06:27.319Z')
            }
        }
    }]

I was trying to write it in spring-data-mongodb without luck.

They do not support $addFields yet, see here.

Since version 2.2.0 RELEASE spring-data-mongodb includes the Aggregation Repository Methods

The above query should be

interface CarRepository extends MongoRepository<Car, String> {

    @Aggregation(pipeline = {
            "{ $addFields : { last:{ $arrayElemAt: [$services,-1] }} }",
            "{ $match: { 'last.scheduled_date_time' : { $gte : '$?0',  $lt: '$?1' } } }"
    })
    List<Car> getCarsWithLastServiceDateBetween(LocalDateTime start, LocalDateTime end);
}

This method logs this query

[{ "$addFields" : { "last" : { "$arrayElemAt" : ["$services", -1]}}}, { "$match" : { "last.scheduled_date_time" : { "$gte" : "$2019-11-03T03:00:00Z", "$lt" : "$2019-11-05T03:00:00Z"}}}]

The date parameters are not parsing correctly. I didn't spend much time making it work.

If you want the Car Ids this could work.

public List<String> getCarsIdWithServicesDateBetween(LocalDateTime start, LocalDateTime end) {

        return template.aggregate(newAggregation(
                unwind("services"),
                group("id").last("services.date").as("date"),
                match(where("date").gte(start).lt(end))
        ), Car.class, Car.class)
                .getMappedResults().stream()
                .map(Car::getId)
                .collect(Collectors.toList());

    }

Query Log

[{ "$unwind" : "$services"}, { "$group" : { "_id" : "$_id", "date" : { "$last" : "$services.scheduled_date_time"}}}, { "$match" : { "date" : { "$gte" : { "$date" : 1572750000000}, "$lt" : { "$date" : 1572922800000}}}}]