Is there a query which would concatenate deep sub-lists?

Question

In my database, I have a list of documents, and each of these documents contains itself another list of documents, the latter being a list of some data with timestamps stored as millis since epoch.

Following is the sample data set:

{
    "_id" : ObjectId("5da5aee33e791547bb418782"),
    "list1" : [
        {
            "document" : {
                "list2" : [
                    {
                        "sub-document" : {
                            "timestamp" : 100
                        }
                    },
                    {
                        "sub-document" : {
                            "timestamp" : 105
                        }
                    },
                    {
                        "sub-document" : {
                            "timestamp" : 110
                        }
                    }
                ]
            }
        },
        {
            "document" : {
                "list2" : [
                    {
                        "sub-document" : {
                            "timestamp" : 101
                        }
                    },
                    {
                        "sub-document" : {
                            "timestamp" : 104
                        }
                    },
                    {
                        "sub-document" : {
                            "timestamp" : 109
                        }
                    }
                ]
            }
        }
    ]
}

Is it possible to query, for instance, 2 latest documents from all the "second-layer" lists?

The expected output is:

[
    {
        "sub-document": {
            "timestamp": 109
        }
    },
    {
        "sub-document": {
            "timestamp": 110
        }
    }
]

user_6238977 · Answer

You need to $unwind twice before you apply $sort and then you can use $limit to get only two elements. You can also run $replaceRoot to promote sub-document to root level of your result:

db.collection.aggregate([
    { $unwind: "$list1" },
    { $unwind: "$list1.document.list2" },
    { $sort: { "list1.document.list2.sub-document.timestamp": -1 } },
    { $limit: 2 },
    { $replaceRoot: { newRoot: "$list1.document.list2.sub-document" } },
    { $sort: { "timestamp": 1 } }
])

Mongo Playground

Is there a query which would concatenate deep sub-lists?

1 Answers