Query data in one queue to get a document with it's own actual version

Question

I have a collection with several documents. The documents might look like this:

{"brand" : "Mars",
"version" : 1}

{"brand" : "Mars",
"version" : 2}

{"brand" : "Bounty",
"version" : 4}

{"brand" : "Snickers",
"version" : 3}

I want to write a query to get just the brand one time with the most actual version. The query should be written in mongoengine or pymongo.

When I filter the collection for example with:

query_result = Sweets.objects.order_by("-version")

I get the following structure:

{"brand" : "Bounty",
"version" : 4}

{"brand" : "Snickers",
"version" : 3}

{"brand" : "Mars",
"version" : 2}

{"brand" : "Mars",
"version" : 1}

But I don't want two documents of "Mars". I just want the most recent one. Is there a way to write one query to get this result:

{"brand" : "Bounty",
"version" : 4}

{"brand" : "Snickers",
"version" : 3}

{"brand" : "Mars",
"version" : 2}

I'm not aware of pymongo, what do you mean by mongoengine ? I can suggest you to look into doing an aggregation stage of $group operation on brand and then return the first matching object from $group stage. — whoami - fakeFaceTrueSoul, Aug 07 '19 at 15:14

score 0 · Answer 1 · answered Aug 07 '19 at 15:23

I found the solution. The MongoDB query looks like this:

db.getCollection("Sweets").aggregate([{"$sort":{"version":-1}},
                                      {"$group"{
                                                "_id": {
                                                     "brand":"$brand"},
                                                "newest_id":{
                                                     "$first":"$_id"}}}])

And then I just filter the collection that I get back from the result collection with find{}.

Query data in one queue to get a document with it's own actual version

1 Answers1