Mongo aggregation: Include a value's description stored in another collection

Question

We have two collections, the first defines files, simplified ex:

{
    _id: "00a00680-0e77-11e7-b757-edf2b0aec1f9",
    name: "someFileName.txt",
    numRows: 17,
    statusCode: 10
},
{
    _id: "0653b830-ac06-11e6-b5e3-7f4580599144",
    name: "someOtherFileName.txt",
    numRows: 134,
    statusCode: 12
},
...

and an associated statusCodes collection:

{
    statusCode: 10,
    statusCodeDesc, "This is the description for status code 10"
},
{
    statusCode: 12,
    statusCodeDesc, "This is the description for status code 12"
}
...

Now, we are using a aggregation and projection in order to produce desired output, currently the projection looks like this:

db.getCollection('files').aggregate([
    {$match: {_id: "00a00680-0e77-11e7-b757-edf2b0aec1f9"}},
    { "$project": {
        "id": "$_id",
        "name": "$name",
        "statusCode": "$statusCode"
    }}
])

which produces the desired output:

{
    _id: "00a00680-0e77-11e7-b757-edf2b0aec1f9",
    name: "someFileName.txt",
    numRows: 17,
    statusCode: 10
}

however what we want is to include the associated status description from the statusCodes collection so that we get this:

{
    _id: "00a00680-0e77-11e7-b757-edf2b0aec1f9",
    name: "someFileName.txt",
    numRows: 17,
    statusCode: 10,
    statusCodeDesc: "This is the description for status code 10"
}

any ideas?

Answer 1

You need $lookup to include values from other collection. As a result you'll get an array of all matching documents from specified collection so you can use $unwind to take first one (as you probably have unique descriptions for each code) and then $project to get final document shape:`

db.files.aggregate([
    {
        $match: {
            _id: "00a00680-0e77-11e7-b757-edf2b0aec1f9"
        }
    },
    {
        $lookup: {
            from: "statusCodes",
            localField: "statusCode",
            foreignField: "statusCode",
            as: "statusCodeDetails"
        }
    },
    {
        $unwind: "$statusCodeDetails"
    },
    {
        $project: {
            _id: 1,
            name: 1,
            numRows: 1,
            statusCode: 1,
            statusCodeDesc: "$statusCodeDetails.statusCodeDesc"
        }
    }
])