MongoDB – Getting the latest item

Lately I have had the pleasure of working with a mongoDB. It´s a nice database with a relatively easy to learn syntax. You can see why it is one of the top favourite kids on the block.

However, that doesn’t mean it won´t be a pain in the ass to get data out of mongoDB that is to your liking.

Imagine you have a list of data that looks like the following:

[
    {"_id": "123", "name": "x", "version": 1, "content": "beautiful content"},
    {"_id": "124", "name": "x", "version": 2, "content": "beautiful content"},
    {"_id": "125", "name": "x", "version": 3, "content": "beautiful content"},
    {"_id": "126", "name": "y", "version": 1, "content": "beautiful content"},
    {"_id": "127", "name": "y", "version": 2, "content": "beautiful content"}
]

Now imagine you want to select all items, but only the latest version. Simple in sql, right? Just do something like “SELECT row from table ORDER BY version DESC LIMIT 1” and you should be good to go. Now in mongoDB it took me a bit longer, although this can be the result of me being a rooky at mongo…

For this you have to use the aggregate. The aggregate accepts several arguments of which we are going to use the following:

<ul>
	<li>sort</li>
	<li>group</li>
	<li>addField</li>
</ul>

Because we want the latest version grouped by the name, we first need to sort the list based on the name and then the version. This way you get a nice list that is already in the correct order. This can be done using the sort option.

Next, we need to create a group. For this we first need to specify the “_id”, which will be the field that is the “unique” identifier for the selection you want to be returned. Then, how to only get the latest version and the corresponding data. Luckily I found a post that allowed me to select the “$first” of the group. Because the data is already sorted, this means that I’ll get the latest version of all items I want to be returned.

Now the last problem that I had to tackle, was the fact that I wanted the original ID (the guide) to be set as “_id” and not as the “name”. If I don’t do this, I don’t have the unique identifier set as “@Id” in my java pojo later on. In order to do this, you can use the “$addFields” option. This refers to the original _id from the object stored in the database itself.

<pre class="brush: java; title: ; notranslate">db.template.aggregate(
     [ 
     	 {$sort:{name:1,version:-1}},
         {$group: { _id: {name:"$name"}, 
             "id": { "$first": "$_id" },
             "name": {"$first":"$name"},
             "content": { "$first": "$content" },
             "version": {"$first":"$version"}
          }},
          {$addFields: {_id: "$id"}} 
     ] 
)

</pre>
This will result in a nice selection that looks exactly like I want it to be:
<pre class="brush: jscript; title: ; notranslate">[
  {"_id": "125", "name": "x", "version": 3, "content": "beautiful content"},
  {"_id": "127", "name": "y", "version": 2, "content": "beautiful content"}
]
</pre>

Now that we have the query working let’s get it into my java Spring project. Basically, you have two options: you can use the spring client (org.springframework.data.mongodb) for this or the mongodb client (com.mongodb.client). Because the spring version allows you to easily convert the result to an object, I chose the first one. With the following code you can query the database.

<pre class="brush: java; title: ; notranslate">           SortOperation sortOperation = Aggregation.sort(
                new Sort(new Sort.Order(Sort.Direction.ASC, "name"), 
                new Sort.Order(Sort.Direction.DESC, "version"))
                );
     
        GroupOperation group = Aggregation.group("name")
                .first("$version").as("version")
                .first("$_id").as("id")
                .first("$name").as("name")
                .first("$content").as("content");

        AggregationOperation addFields = new AggregationOperation() {
            @Override
            public Document toDocument(AggregationOperationContext aoc) {
                return new Document("$addFields", new Document("_id", "$id"));
            }
        };

        Aggregation aggregation
                = Aggregation.newAggregation(sortOperation,group,addFields);

        AggregationResults myObjectsResult = mongoTemplate.aggregate(aggregation, "myCollection", MyObject.class);
        List myObjects = StreamSupport.stream(Spliterators.spliteratorUnknownSize(myObjectsResult.iterator(), 
            Spliterator.ORDERED), false).collect(Collectors.toList());
</pre>

That’s it. Have fun querying!