MongoDB: Joining collections into single document

Question

I am looking for some information about joins and how they behave within MongoDB. While I appreciate that a large amount of the power behind a NoSQL database is to store all information in document, and that document to contain as much information as possible so that you don't have to do multiple queries

However I also appreciate that there are some scenarios where you may want to minimise the duplication of data so that you minimise the risk of consistency issues.

When using document references, is there any way to return a single document? I have tried the $lookup function, and while it does return me the right result, it returns one document for every array entry.

Assuming the following structure:

Customer:

{
    _id: ObjectId("578d706916f07969c4b0cad1"),
    name: "fred",
    orders: [ObjectId("578d706916f07969c4b0cad2"),ObjectId("578d706916f07969c4b0cad3")]
}

Orders:

{
    _id: ObjectId("578d706916f07969c4b0cad2"),
    date: xxxx,
    items: [a,b,c,d]
},
{
    _id: ObjectId("578d706916f07969c4b0cad2"),
    date: xxxx,
    items: [x,y,z]
}

Using lookup, I can get a result of 2 documents, one for each order with the customer data appended, however I am looking for a single document with an array or order information - an embedded document. i.e. the following structure

{
    _id: ObjectId("578d706916f07969c4b0cad1"),
    name: "fred",
    orders: [
        {
            _id: ObjectId("578d706916f07969c4b0cad2"),
            date: xxxx,
            items: [a,b,c,d]
        },
        {
            _id: ObjectId("578d706916f07969c4b0cad2"),
            date: xxxx,
            items: [x,y,z]
        }]
}

Is this possible within MongoDB? Or is the join process so closely matched to SQL databases that you will always return the duplicated customer information when trying to join data? I know that the data could be turned into a single document within an application such as node however is it possible, from the way the query is written, to return it pre-formatted?

score 1 · Accepted Answer · answered Jul 27 '16 at 12:40

You can perform a single aggregation to get that result. One part of mongo $lookup documentation show how to lookup with an array.

Given the following collections :

db.user.insert({
    name: "fred",
    orders: [ObjectId("578d706916f07969c4b0cad2"),ObjectId("578d706916f07969c4b0cad3")]
})
db.order.insert({
    _id: ObjectId("578d706916f07969c4b0cad2"),
    date: ISODate("2012-12-20T06:01:17.171Z"),
    items: ["a","b","c","d"]
})

db.order.insert({
    _id: ObjectId("578d706916f07969c4b0cad3"),
    date: ISODate("2012-12-19T06:01:17.171Z"),
    items: ["x","y","z"]
})

Perform an aggregation with :

$unwind of your orders array
$lookup for order collection on _id field
$unwind newly created orders field containing looked up items
$group by _id and regroup your orders items in a single array without duplicate

aggregation query :

db.user.aggregate([{
    $unwind: "$orders"
}, {
    $lookup: {
        from: "order",
        localField: "orders",
        foreignField: "_id",
        as: "orders"
    }
}, {
    $unwind: "$orders"
}, {
    $group: {
        _id: "$_id",
        name: {
            $first: "$name"
        },
        orders: {
            $addToSet: "$orders"
        }
    }
}])

This will give you :

{
    "_id": ObjectId("5798a8e1968539bb0a98d1c3"),
    "name": "fred",
    "orders": [{
        "_id": ObjectId("578d706916f07969c4b0cad3"),
        "date": ISODate("2012-12-19T06:01:17.171Z"),
        "items": ["x", "y", "z"]
    }, {
        "_id": ObjectId("578d706916f07969c4b0cad2"),
        "date": ISODate("2012-12-20T06:01:17.171Z"),
        "items": ["a", "b", "c", "d"]
    }]
}

MongoDB: Joining collections into single document

1 Answers1