Getting a list of unique embedded/nested objects i

问题:

Consider the following MongoDB "recipes" collection:

{
  "title" : "Macaroni and Cheese",
  "ingredients" : [
    { "name" : "noodles", "qty" : "2 c" },
    { "name" : "butter", "qty" : "2 tbl" },
    { "name" : "cheese", "qty" : "1 c" },
  ]
},
{
  "title" : "Pound Cake",
  "ingredients" : [
    { "name" : "sugar", "qty" : "1 lb" },
    { "name" : "butter", "qty" : "1 lb" },
    { "name" : "flour", "qty" : "1 lb" },
  ]
},
{
  "title" : "Dough",
  "ingredients" : [
    { "name" : "water", "qty" : "2 c" },
    { "name" : "butter", "qty" : "8 tbl" },
    { "name" : "flour", "qty" : "1 lb" },
  ]
}

I wish to write a query to generate a "shopping list" of items to buy to make all the recipes. So I basically want to return the ingredients "noodles", "butter", "cheese", "sugar", "butter", "flour", "water". I don't want duplicates. (Sugar and butter, for example appear in more than one recipe, but I only want to return them once, i.e. no duplicates.)

Is it possible to create such a query in MongoDB and if so, what would this query be? Or would it necessitate me to create a separate collection for "ingredients"?

回答1:

The following query would give you the bare list minus any duplicates:

db.recipes.aggregate([{$unwind:"$ingredients"},{$group:{_id:"$ingredients.name"}}])

More work would be necessary to add the quantities. It would be easier if the unit for the quantities was specified separately.

回答2:

Use distinct to find an array of distinct values for ingredients.name

db.recipes.distinct('ingredients.name')

yields [ "butter", "cheese", "noodles", "flour", "sugar", "water" ]

回答3:

Here is the full aggregate pipeline you were looking for:

db.recipes.aggregate([
       {$unwind:"$ingredients"},
       {$group:{_id:"$ingredients.name",
                quantity:{$first:"$ingredients.qty"},
                recipes:{$push:"$title"},
                total:{$sum:1}
               }
       },
       {$project:{_id:0,ingredient:"$_id", total:1, quantity:1, recipes:1}
])

This unwinds the arrays, groups by ingredient, adds them up to see how many recipes need them, keeps the quantity fields which is going to be the same in your use case, and adds a list of recipes it's used in. Last step renames the grouped on field to "ingredient".

Result:

{ "quantity" : "1 lb", "recipes" : [  "Pound Cake",  "Dough" ], "total" : 2, "ingredient" : "flour" }
{ "quantity" : "2 c", "recipes" : [  "Dough" ], "total" : 1, "ingredient" : "water" }
{ "quantity" : "1 lb", "recipes" : [  "Pound Cake" ], "total" : 1, "ingredient" : "sugar" }
{ "quantity" : "1 c", "recipies" : [  "Macaroni and Cheese" ], "total" : 1, "ingredient" : "cheese" }
{ "quantity" : "2 tbl", "recipes" : [  "Macaroni and Cheese",  "Pound Cake",  "Dough" ], "total" : 3, "ingredient" : "butter" }
{ "quantity" : "2 c", "recipes" : [  "Macaroni and Cheese" ], "total" : 1, "ingredient" : "noodles" }

回答4:

A funny way of doing it in the mongoshell using foreach operator

var list = [];
db.recepies.find(
  {},
  {'ingredients.name' : 1, _id : 0}
).forEach(function(doc){
  var ingredients = doc.ingredients;
  for (var i=0; i< ingredients.length; i++){
    var ingredient = ingredients[i].name;
    if (list.indexOf(ingredient) == -1){
       list.push(ingredient)
    }
  }
});

after this list will contain all the elements. P.S. I am sure this is also possible with aggregation framework.