Join-like operation in MongoDB

SQL databases provide JOIN operation: each row in the query result is provided with the related data found by the foreign key value. The similar effect could be achieved in MongoDB if store child items as array in a collection record and use aggregate / unwind function.

Example data

Let's have orders collection where each record contains array of items purchased:

> db.orders.save({
    u:1,
    items:[{name:'Pencil', price:0.25}, {name: 'Pen', price: 0.19}]
})
WriteResult({ "nInserted" : 1 })

> db.orders.save({
    u:2,
    items:[{name:'Book', price:0.57}, {name: 'Pen', price: 0.19}]
})
WriteResult({ "nInserted" : 1 })

> db.orders.find({}, {_id: 0})
{"u": 1, "items": [{ "name": "Pencil", "price": 0.25 }, { "name": "Pen", "price" :0.19 }]}
{"u": 2, "items": [{"name": "Book", "price": 0.57 }, { "name": "Pen", "price": 0.19 }]}

The second parameter tells to exclude the _id column from the output.

Query data

How to get items from several orders with the parent order record, like we join them:

> db.orders.aggregate([{$unwind: '$items'}])
{ "_id" : ObjectId("...2e"), "u" : 1, "items" : { "name" : "Pencil", "price" : 0.25 } }
{ "_id" : ObjectId("...2e"), "u" : 1, "items" : { "name" : "Pen", "price" : 0.19 } }
{ "_id" : ObjectId("...2f"), "u" : 2, "items" : { "name" : "Book", "price" : 0.57 } }
{ "_id" : ObjectId("...2f"), "u" : 2, "items" : { "name" : "Pen", "price" : 0.19 } }

The number of results is same as the number of items, each item is returned with its parent order.

We can make grouping on the item name then to calculate sum per item:

> db.orders.aggregate([
  {$unwind: '$items'},
  {
     $group: {
       _id: "$items.name",
       avg:{$sum: '$items.price'}
     }
  }
])
{ "_id" : "Pencil", "avg" : 0.25 }
{ "_id" : "Book", "avg" : 0.57 }
{ "_id" : "Pen", "avg" : 0.38 }