Mongodb embedded document - aggregation query

Question

I have got the below documents in Mongo database:

db.totaldemands.insert({ "data" : "UKToChina", "demandPerCountry" : 
 { "from" : "UK" , to: "China" , 
   "demandPerItem" : [ { "item" : "apples" , "demand" : 200 }, 
   { "item" : "plums" , "demand" : 100 }
] } });

db.totaldemands.insert({ "data" : "UKToSingapore", 
"demandPerCountry" : { "from" : "UK" , to: "Singapore" , 
"demandPerItem" : [ { "item" : "apples" , "demand" : 100 }, 
  { "item" : "plums" , "demand" : 50 }
] } });

I need to write a query to find the count of apples exported from UK to any country.

I have tried the following query:

db.totaldemands.aggregate(
{ $match : { "demandPerCountry.from" : "UK" ,  
  "demandPerCountry.demandPerItem.item" : "apples" } },
  { $unwind : "$demandPerCountry.demandPerItem" },
  { $group : { "_id" : "$demandPerCountry.demandPerItem.item", 
  "total" : { $sum : "$demandPerCountry.demandPerItem.demand" 
  } } }
);

But it gives me the output with both apples and plums like below:

{ "_id" : "apples", "total" : 300 }
{ "_id" : "plums", "total" : 150 }

But, my expected output is:

 { "_id" : "apples", "total" : 300 }

So, How can I modify the above query to return only the count of apples exported from UK ? Also, is there any other better way to achieve the output without unwinding ?

notionquest · Accepted Answer · 2016-08-31 16:41:25Z

1

You can add another $match to get only apples.

As you have embedded document structure and performing aggregation, $unwind is required here. The alternate option could be map and reduce. However, unwind is most suitable here.

If you are thinking about performance, unwind shouldn't cause performance issue.

db.totaldemands.aggregate(
{ $match : { "demandPerCountry.from" : "UK" ,  
  "demandPerCountry.demandPerItem.item" : "apples" } },
  { $unwind : "$demandPerCountry.demandPerItem" },
  { $group : { "_id" : "$demandPerCountry.demandPerItem.item", 
  "total" : { $sum : "$demandPerCountry.demandPerItem.demand" 
  } } },
  {$match : {"_id" : "apples"}}
);

edited Aug 31, 2016 at 16:41

answered Aug 31, 2016 at 16:32

notionquest

39.4k6 gold badges120 silver badges111 bronze badges

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

Collectives™ on Stack Overflow

Mongodb embedded document - aggregation query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related