CODEWITHSUNDEEP
mongodb
user3879526
user3879526

Reputation: 11

Single query for fetching latest documents in MongoDB

Need a single query for fetching latest documents - MongoDB

I have a MongoDB collection like this,

db.file_info.find()

{ "_id" : A, "fileID" : 0, "size" : 126, "version" : 1}

{ "_id" : B, "fileID" : 1, "size" : 126, "version" : 1}

{ "_id" : C, "fileID" : 2, "size" : 121, "version" : 1}

{ "_id" : D, "fileID" : 1, "size" : 124, "version" : 2}

{ "_id" : E, "fileID" : 3, "size" : 125, "version" : 2}

{ "_id" : F, "fileID" : 2, "size" : 120, "version" : 3}

{ "_id" : G, "fileID" : 4, "size" : 122, "version" : 3}

I have to query the latest version of fileID's sort by version.

I need a result like this,

{ "_id" : G, "fileID" : 4, "size" : 122, "version" : 3}

{ "_id" : E, "fileID" : 3, "size" : 125, "version" : 2}

{ "_id" : F, "fileID" : 2, "size" : 120, "version" : 3}

{ "_id" : D, "fileID" : 1, "size" : 124, "version" : 2}

{ "_id" : A, "fileID" : 0, "size" : 126, "version" : 1}

I couldn't find the query which gives the aforementioned result. Currently we are overcoming this problem as this way:

db.file_info.find({"version":{$lte:3}}).sort({"version":1});

It will list the all documents orderly which are lesser than version 3. Then we are processing the result-set in memory and get the latest version. But this is not scalable for larger collections. If we can perform this as a single query, It will be better for my project.

Upvotes: 1

Views: 86

Answers (2)

JohnnyHK
JohnnyHK

Reputation: 311835

You would do this with aggregate, not find:

db.file_info.aggregate([
    // Descending sort of all docs on version
    {$sort: {version: -1}},
    // Group the docs by fileID, taking the fields from the first doc in each
    // group. Because of the previous sort, this gets the fields from the doc
    // with the highest version.
    {$group: {
        _id: '$fileID', 
        origid: {$first: '$_id'}, 
        size: {$first: '$size'}, 
        version: {$first: '$version'}
    }},
    // Reshape the docs to restore the original structure.
    {$project: {
        _id: '$origid', 
        fileID: '$_id', 
        size: '$size', 
        version: '$version'
    }}
])

Output

{
    "result" : [ 
        {
            "_id" : "A",
            "size" : 126,
            "version" : 1,
            "fileID" : 0
        }, 
        {
            "_id" : "E",
            "size" : 125,
            "version" : 2,
            "fileID" : 3
        }, 
        {
            "_id" : "D",
            "size" : 124,
            "version" : 2,
            "fileID" : 1
        }, 
        {
            "_id" : "G",
            "size" : 122,
            "version" : 3,
            "fileID" : 4
        }, 
        {
            "_id" : "F",
            "size" : 120,
            "version" : 3,
            "fileID" : 2
        }
    ],
    "ok" : 1
}

Upvotes: 1

AK_
AK_

Reputation: 71

db.file_info.aggregate({$group: {_id : '$_id', fileID: '$fileID', size: '$size', version: {$max: '$version'}}})

Upvotes: 0

Related Questions