Conditional/max mongo queries using spring mongoTemplate

Question

I have hundreds of records like these in a mongodb collection:

{
    "city" : "London",
    "eventTime": 1582866000,
    "createdTime": 1582900145,
    "eventDetails: {
        ...
    }
}

This is corresponding the Event class

public class Event {
    private String city;
    private long eventTime;
    private long createdTime;
    private EventDetails eventDetails;

    //getters and setters
}

-eventTime (all time values are in local epoch/unix time) will always be equal to an hour
-createdTime is being used as version control (greater the createdTime, more recent the version of this information)
- not using update/upsert as I have uses for keeping older information

I want to be able to query something like this:

1) given a version timestamp - return a list of objects (the entire object) for each city, for each eventTime, where timestamp is closest to (floor or celing) createdTime
2) same as 1) but for max(createdTime) - for each city and every eventTime

Essentially - there will be a lot of duplicates for each city, for each eventTime due to multiple versions, and I only want the version that I specify, and the latest version

I'm trying to do these two queries using mongoTemplate in java (spring mongo) I've tried messing around with Query, Aggregation, AggregationOperator.Max but nothing seems to be working

Aggregation aggregation = Aggregation.newAggregation(
Aggregation.match(Criteria.where("city").is(city)),
Aggregation.match(Criteria.where("eventTime").gte(startTime).lte(endTime)),
Aggregation.group("$$ROOT").max("createdTime").as("createdTime")
);
AggregationResults<Event> groupResults = mongoTemplate.aggregate(aggregation, "collection-name", Event.class);
result = groupResults.getMappedResults();

sample data and expected outputs: https://script.google.com/d/1JgOFzdVBGiueYpXT8u-R6AJpbX6FxxtkVwq_NpLraZeS19Pxh6zmnATb/edit?usp=sharing

I have also tried doing these queries in mongodb shell and haven't been successful with that either, it's similar but it's not returning the object structure that I want

db.getCollection('collection-name').aggregate([{$group:{_id : "$$ROOT", createdTime : {$max:"$createdTime"}}}])

Answer 1

Max

db.collection.aggregate([
  {
    $group: {
      _id: {
        city: "$city",
        eventTime: "$eventTime"
      },
      max: {
        $max: "$createdTime"
      },
      data: {
        $push: "$$ROOT"
      }
    }
  },
  {
    $project: {
      max_events: {
        $filter: {
          input: "$data",
          cond: {
            $eq: [
              "$max",
              "$$this.createdTime"
            ]
          }
        }
      }
    }
  },
  {
    $unwind: "$max_events"
  },
  {
    $replaceRoot: {
      newRoot: "$max_events"
    }
  }
])

MongoPlayground

MongoTemplate

import static org.springframework.data.mongodb.core.aggregation.Aggregation.*;

Aggregation agg = Aggregation.newAggregation(
    group("city", "eventTime").max("createdTime").as("max").push("$$ROOT").as("data"),
    project().and(new AggregationExpression() {
        @Override
        public Document toDocument(AggregationOperationContext context) {
            return new Document("$filter",
                    new Document("input", "$data")
                         .append("cond", new Document("$eq", Arrays.asList("$max", "$$this.createdTime"))));
        }
    }).as("max_events"),
    unwind("max_events"),
    replaceRoot("max_events")
);

AggregationResults<Event> events = mongoTemplate.aggregate(agg, mongoTemplate.getCollectionName(Event.class), Event.class);

for(Event ev: events) {
    System.out.println(ev);
}

Closest

db.collection.aggregate([
  {
    $group: {
      _id: {
        city: "$city",
        eventTime: "$eventTime"
      },
      data: {
        $push: "$$ROOT"
      }
    }
  },
  {
    $project: {
      closest: {
        $reduce: {
          input: "$data",
          initialValue: {
            $arrayElemAt: [
              "$data",
              0
            ]
          },
          in: {
            $cond: [
              {
                $lt: [
                  {
                    $abs: {
                      $subtract: [
                        "$$this.eventTime",
                        "$$this.createdTime"
                      ]
                    }
                  },
                  {
                    $abs: {
                      $subtract: [
                        "$$value.eventTime",
                        "$$value.createdTime"
                      ]
                    }
                  }
                ]
              },
              "$$this",
              "$$value"
            ]
          }
        }
      }
    }
  },
  {
    $unwind: "$closest"
  },
  {
    $replaceRoot: {
      newRoot: "$closest"
    }
  }
])

MongoPlayground

MongoTemplate

Aggregation agg = Aggregation.newAggregation(
    group("city", "eventTime").push("$$ROOT").as("data"),
    project().and(new AggregationExpression() {
        @Override
        public Document toDocument(AggregationOperationContext context) {
            return new Document("$reduce",
                    new Document("input", "$data")
                     .append("initialValue", new Document("$arrayElemAt", Arrays.asList("$data", 0)))
                     .append("in", new Document("$cond", Arrays.asList(
                            new Document("$lt",  Arrays.asList(
                                new Document("$abs", new Document("$subtract", Arrays.asList("$$this.eventTime", "$$this.createdTime"))), 
                                new Document("$abs", new Document("$subtract", Arrays.asList("$$value.eventTime", "$$value.createdTime"))))), 
                            "$$this",
                            "$$value"))));
        }
    }).as("closest"),
    unwind("closest"),
    replaceRoot("closest")
);

AggregationResults<Event> events = mongoTemplate.aggregate(agg, mongoTemplate.getCollectionName(Event.class), Event.class);

for(Event ev: events) {
    System.out.println(ev);
}