Aggregation framework flatten subdocument data with parent document

Question

I am building a dashboard that rotates between different webpages. I am wanting to pull all slides that are part of the "Test" deck and order them appropriately. After the query my result would ideally look like.

[
    { "url" : "http://10.0.1.187", "position": 1, "duartion": 10 },
    { "url" : "http://10.0.1.189", "position": 2, "duartion": 3 }
]

I currently have a dataset that looks like the following

{
    "_id" : ObjectId("53a612043c24d08167b26f82"),
    "url" : "http://10.0.1.189",
    "decks" : [
        {
            "title" : "Test",
            "position" : 2,
            "duration" : 3
        }
    ]
}
{
    "_id" : ObjectId("53a6103e3c24d08167b26f81"),
    "decks" : [
        {
            "title" : "Test",
            "position" : 1,
            "duration" : 2
        },
        {
            "title" : "Other Deck",
            "position" : 1,
            "duration" : 10
        }
    ],
    "url" : "http://10.0.1.187"
}

My attempted query looks like:

db.slides.aggregate([
    {
        "$match": {
            "decks.title": "Test"
        }
    },
    {
        "$sort": {
            "decks.position": 1
        }
    },
    {
        "$project": {
            "_id": 0,
            "position": "$decks.position",
            "duration": "$decks.duration",
            "url": 1
        }
    }
]);

But it does not yield my desired results. How can I query my dataset and get my expected results in a optimal way?

Answer 1

Well to truly "flatten" the document as your title suggests then $unwind is always going to be employed as there really is not other way to do that. There are however some different approaches if you can live with the array being filtered down to the matching element.

Basically speaking, if you really only have one thing to match in the array then your fastest approach is to simply use .find() matching the required element and projecting:

 db.slides.find(
     { "decks.title": "Test" },
     { "decks.$": 1 }
 ).sort({ "decks.position": 1 }).pretty()

That is still an array but as long as you have only one element that matches then this does work. Also the items are sorted as expected, though of course the "title" field is not dropped from the matched documents, as that is beyond the possibilities for simple projection.

{
    "_id" : ObjectId("53a6103e3c24d08167b26f81"),
    "decks" : [
            {
                    "title" : "Test",
                    "position" : 1,
                    "duration" : 2
            }
    ]
}
{
    "_id" : ObjectId("53a612043c24d08167b26f82"),
    "decks" : [
            {
                    "title" : "Test",
                    "position" : 2,
                    "duration" : 3
            }
    ]
}

Another approach, as long as you have MongoDB 2.6 or greater available, is using the $map operator and some others in order to both "filter" and re-shape the array "in-place" without actually applying $unwind:

db.slides.aggregate([
    { "$project": {
        "url": 1,
        "decks": {
            "$setDifference": [
                { 
                    "$map": {
                        "input": "$decks",
                        "as": "el",
                        "in": {
                            "$cond": [
                                { "$eq": [ "$$el.title", "Test" ] },
                                { 
                                    "position": "$$el.position",
                                    "duration": "$$el.duration"
                                },
                                false
                            ]
                        }
                    }
                },
                [false]
            ]
        }
    }},
    { "$sort": { "decks.position": 1 }}
])

The advantage there is that you can make the changes without "unwinding", which can reduce processing time with large arrays as you are not essentially creating new documents for every array member and then running a separate $match stage to "filter" or another $project to reshape.

{
    "_id" : ObjectId("53a6103e3c24d08167b26f81"),
    "decks" : [
            {
                    "position" : 1,
                    "duration" : 2
            }
    ],
    "url" : "http://10.0.1.187"
}
{
    "_id" : ObjectId("53a612043c24d08167b26f82"),
    "url" : "http://10.0.1.189",
    "decks" : [
            {
                    "position" : 2,
                    "duration" : 3
            }
    ]
}

You can again either live with the "filtered" array or if you want you can again "flatten" this truly by adding in an additional $unwind where you do not need to filter with $match as the result already contains only the matched items.

But generally speaking if you can live with it then just use .find() as it will be the fastest way. Otherwise what you are doing is fine for small data, or there is the other option for consideration.

Answer 2

Well as soon as I posted I realized I should be using an $unwind. Is this query the optimal way to do it, or can it be done differently?

db.slides.aggregate([
    {
        "$unwind": "$decks"
    },
    {
        "$match": {
            "decks.title": "Test"
        }
    },
    {
        "$sort": {
            "decks.position": 1
        }
    },
    {
        "$project": {
            "_id": 0,
            "position": "$decks.position",
            "duration": "$decks.duration",
            "url": 1
        }
    }
]);