Aggregate Query in MongoDB

Question

I am new to MongoDB. I have a requirement to get the latest updated product for particular date from a array of products. Below is the JSON object of my requirement.

{
    "_id": ObjectId("10001"),
    "product": [{
        "image": "./../../../prod1.html",
        "name": "product",
        "version": 1,
        "updatedDate": "14-03-2017"
    }, {
        "image": "./../../../prod1.html",
        "name": "product",
        "version": 2,
        "updatedDate": "14-03-2017"
    }, {
        "image": "./../../../prod1.html",
        "name": "product",
        "version": 1,
        "updatedDate": "15-03-2017"
    }, {
        "image": "./../../../prod1.html",
        "name": "product",
        "version": 2,
        "updatedDate": "15-03-2017"
    }, {
        "image": "./../../../prod1.html",
        "name": "product",
        "version": 3,
        "updatedDate": "15-03-2017"
    }, {
        "image": "./../../../prod1.html",
        "name": "product",
        "version": 4,
        "updatedDate": "15-03-2017"
    }]
}, {
    "_id": ObjectId("10002"),
    "product": [{
        "image": "./../../../prod1.html",
        "name": "product",
        "version": 1,
        "updatedDate": "14-03-2017"
    }, {
        "image": "./../../../prod1.html",
        "name": "product",
        "version": 2,
        "updatedDate": "14-03-2017"
    }, {
        "image": "./../../../prod1.html",
        "name": "product",
        "version": 1,
        "updatedDate": "15-03-2017"
    }, {
        "image": "./../../../prod1.html",
        "name": "product",
        "version": 2,
        "updatedDate": "15-03-2017"
    }, {
        "image": "./../../../prod1.html",
        "name": "product",
        "version": 3,
        "updatedDate": "15-03-2017"
    }]
},
}

I need a query - which will retrieve a product with 14-03-2017 with latest version.

Expected Result is:

{
"_id" : ObjectId("10001"),
"product" : [
    {"image" : "./../../../prod1.html","name" : "product","version" : 4,"updatedDate":"15-03-2017"} 
]},
 {
"_id" : ObjectId("10002"),
"product" : [
    {"image" : "./../../../prod1.html","name" : "product","version" : 3,"updatedDate":"15-03-2017"} 
]}

It would be highly appreciate some one can help me with the aggregate function to get the expected outcome.

Need to fetch the product with latest date and latest version in that date(Date will contain with multiple versions) — yuvan
– yuvan, Commented Mar 15, 2017 at 13:32

radhakrishnan · Accepted Answer · 2017-03-15 13:55:53Z

1

Use aggregate command,

db.collection.aggregate([{
    $unwind: "$product"
}, {
    $sort: {
        "product.updatedDate": 1
    }
}, {
    $group: {
        _id: "$_id",
        product: {
            $last: "$product"
        }
    }
}])

Output:

{
    "_id" : ObjectId("10002"),
    "product" : {
        "image" : "./../../../prod1.html",
        "name" : "product",
        "version" : 3,
        "updatedDate" : "15-03-2017"
    }
}
{
    "_id" : ObjectId("10001"),
    "product" : {
        "image" : "./../../../prod1.html",
        "name" : "product",
        "version" : 4,
        "updatedDate" : "15-03-2017"
    }
}

answered Mar 15, 2017 at 13:55

radhakrishnan

1,4791 gold badge15 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

yuvan Over a year ago

Hello Friend, Thanks a lot. It worked perfectly. Thanks for your immediate respond.

Collectives™ on Stack Overflow

Aggregate Query in MongoDB

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related