CODEWITHSUNDEEP
mongodbaggregation-framework
user29578
user29578

Reputation: 689

Mongodb aggregate query on specific records instead of collection

i am using the aggregate query to find all orders for all customers using this query

> db.orders.aggregate([{ "$group":{ "_id":"$customer", "orders":{ "$sum": 1 }}}])
{ "_id" : "b", "orders" : 2 }
{ "_id" : "a", "orders" : 3 }

but now instead of running this query on all orders records , i just want to run it on specific set of orders , which is returned by this query 

   db.delivery.find({"status":"DELIVERED"},{order:1}).pretty() ,

which gives me 

{ "order" : ObjectId("551c5381e4b0df29878547e1") } 
{ "order" : ObjectId("551c8f8ae4b0ab335af6ab91") } 
{ "order" : ObjectId("551ca7ede4b0ab335af6ab95") } 
{ "order" : ObjectId("551cb00fe4b0ab335af6ab98") } 
{ "order" : ObjectId("551cbe20e4b0df29878547ed") }
....and few more records

how can i achieve this ,help

Upvotes: 2

Views: 311

Answers (1)

chridam
chridam

Reputation: 103455

Use the $match operator to filter the documents getting into your pipeline.

Get the list of order ids (to use in the $match pipeline with $in) by using the find() cursor's map() method:

var orderIds = db.delivery.find({"status": "DELIVERED"}).map(function(d){return d.order;});
db.orders.aggregate([
    { "$match": { "_id": { "$in": orderIds } } },
    { "$group": { "_id": "$customer", "orders": { "$sum": 1 } } }
])

For MongoDB 3.2, use the $lookup operator which does a left outer join to an unsharded collection in the same database to filter in documents from the "joined" collection for processing.

The following example shows how you can run the aggregation operation on the orders collection joining the documents from orders with the documents from the delivery collection using the field order from the delivery collection:

db.orders.aggregate([
    {
        "$lookup": {
            "from": "delivery",
            "localField": "_id",
            "foreignField": "order",
            "as": "delivery_orders"
        }
    },
    { "$match": { "delivery_orders.status": "DELIVERED" } },    
    { "$group": { "_id": "$customer", "orders": { "$sum": 1 } } }
])

Upvotes: 3

Related Questions