mongo $unwind and $group

aggregation-framework, mongodb, mongodb-query, nosql

Solution

Since you had initially pivoted your original documents on the `tags` array field which means the documents will be denormalized, your `$group` pipeline should use the `_id` field as its `_id` key and access the other fields using the `$first` or `$last` operator.

The group pipeline operator is similar to the SQL's `GROUP BY` clause. In SQL, you can't use `GROUP BY` unless you use any of the aggregation functions. The same way, we have to use an aggregation function in MongoDB as well, so unfortunately there is no other way of not having to define in the `$group` pipeline all the fields which you wish to return apart from using the `$first` or `$last` operator on each field:

db.tracks.aggregate([
    { "$unwind": "$tags" }, 
    {
        "$lookup": {
            "from": "tags",
            "localField": "tags",
            "foreignField": "_id",
            "as": "resultingArray"
        }
    }, 
    { "$unwind": "$resultingArray" },
    {
        "$group": {
            "_id": "$_id",
            "title": { "$first": "$title" },
            "uid": { "$first": "$uid" },
            "created_at": { "$first": "$created_at" },
            "updated_at": { "$first": "$updated_at" },
            "id": { "$first": "$id" },
            "metadata": { "$first": "$metadata" },
            "tags": { "$push": "$resultingArray" }
        }
    }
])

One trick I always use whenever I want to debug a pipeline that's giving unexpected results is to run the aggregation with just the first pipeline operator. If that gives the expected result, add the next.

In the answer above, you'd first try aggregating just the `$unwind`; if that works, add the `$lookup`. This can help you narrow down which operator is causing issues. In this case, you could run the pipeline with just the first three steps since you believe the `$group` is the one causing issues and then inspect the resulting documents from that pipeline:

db.tracks.aggregate([
    { "$unwind": "$tags" }, 
    {
        "$lookup": {
            "from": "tags",
            "localField": "tags",
            "foreignField": "_id",
            "as": "resultingArray"
        }
    }, 
    { "$unwind": "$resultingArray" }
])

which yields the output

/* 1 */
{
    "_id" : ObjectId("5804a6c900ce8cbd028523d9"),
    "title" : "Some Title",
    "uid" : "some-title",
    "created_at" : "1412159926",
    "updated_at" : "1412159926",
    "id" : "1",
    "metadata" : {
        "date" : "2016-10-17",
        "description" : "a description"
    },
    "resultingArray" : {
        "name" : "Tag 1",
        "uid" : "tag-1"
    }
}

/* 2 */
{
    "_id" : ObjectId("5804a6c900ce8cbd028523d9"),
    "title" : "Some Title",
    "uid" : "some-title",
    "created_at" : "1412159926",
    "updated_at" : "1412159926",
    "id" : "1",
    "metadata" : {
        "date" : "2016-10-17",
        "description" : "a description"
    },
    "resultingArray" : {
        "name" : "Tag 2",
        "uid" : "tag-2"
    }
}

/* 3 */
{
    "_id" : ObjectId("5804a6c900ce8cbd028523d9"),
    "title" : "Some Title",
    "uid" : "some-title",
    "created_at" : "1412159926",
    "updated_at" : "1412159926",
    "id" : "1",
    "metadata" : {
        "date" : "2016-10-17",
        "description" : "a description"
    },
    "resultingArray" : {
        "name" : "Tag 3",
        "uid" : "tag-3"
    }
}

From inspection you will see that for each input document, the last pipeline outputs 3 documents where 3 is the number of array elements in the computed field `resultingArray` and they all have a common `_id` and the other fields with the exception of the `resultingArray` field which is different, thus you get your desired results by adding a pipeline that groups the documents by the `_id` field and subsequently getting the other fields with `$first` or `$last` operator, as in the given solution:

db.tracks.aggregate([
    { "$unwind": "$tags" }, 
    {
        "$lookup": {
            "from": "tags",
            "localField": "tags",
            "foreignField": "_id",
            "as": "resultingArray"
        }
    }, 
    { "$unwind": "$resultingArray" },
    {
        "$group": {
            "_id": "$_id",
            "title": { "$first": "$title" },
            "uid": { "$first": "$uid" },
            "created_at": { "$first": "$created_at" },
            "updated_at": { "$first": "$updated_at" },
            "id": { "$first": "$id" },
            "metadata": { "$first": "$metadata" },
            "tags": { "$push": "$resultingArray" }
        }
    }
])

Problem

Original source