Using $slice when finding data from subdocument's array

Question

I need to get the latest entry from subdocument's array from a document defined by "_id".

The document looks like this:

{
"_id": "nex67",
"ownedparts": [
    {
        "id": "tool1",
        "history": [
            {
                "time": ISODate("2016-06-07T09:12:54.015Z"),
                "value": 300
            },
            {
                "time": ISODate("2016-06-07T09:12:54.015Z"),
                "value": 240
            }
        ]
    },
    {
        "id": "screw1",
        "history": [
            {
                "time": ISODate("2016-06-07T09:12:54.015Z"),
                "value": 500
            }
        ]
    }
]}

With this query i get full history of "tool1":

db.users.find(  
{
    "_id": "nex67",
    "ownedparts.id": "tool1"
},  { "ownedparts.history.$": 1 })

And with this command i do get the latest entry, but it returns them from both "tool1" and "screw1":

db.users.find(  
{"_id": "nex67"},
{"ownedparts.history": { $slice: -1 }})

So basically i need help combining these queries so that i can get the latest history entry from nex67's tool1.

The result i need should look like this:

{
    "_id" : "nex67",
    "ownedparts" : [
            {
                    "id" : "tool1",
                    "history" : [
                            {
                                    "time" : ISODate("2016-06-07T09:12:54.015Z"),
                                    "value" : 240
                            }
                    ]
            }
    ]}

Sede · Accepted Answer · 2016-06-08 12:34:32Z

2

The optimal way to do this is in MongoDB 3.2 using the $arrayElemAt and the $filter operators.

db.users.aggregate(
    [
        { "$match": { "_id": "nex67", "ownedparts.id": "tool1" } }, 
        { "$project": { 
            "ownedparts": { 
                "$let": { 
                    "vars": { 
                        "el": { 
                            "$arrayElemAt": [ 
                                { "$filter": { 
                                    "input": "$ownedparts", 
                                    "as": "own", 
                                    "cond": { "$eq": [ "$$own.id", "tool1" ] }
                                }}, 
                                0 
                            ]
                        }
                    }, 
                    "in": { 
                        "id": "$$el.id", 
                        "history": { "$arrayElemAt": [ "$$el.history", -1 ] } 
                    }
                }
            }
        }}
    ]
)

which produces:

{ 
    "_id" : "nex67", 
    "ownedparts" : { 
        "id" : "tool1", 
        "history" : {
            "time" : ISODate("2016-06-07T09:12:54.015Z"), 
            "value" : 240 
        } 
    }
}

edited Jun 8, 2016 at 12:34

answered Jun 8, 2016 at 12:00

Sede

61.5k20 gold badges158 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

profesor79 Over a year ago

nice one - perfect combination of $filter inside $arrayElemAt!

profesor79 · Accepted Answer · 2016-06-07 21:45:04Z

1

As we have nested arrays - it is not a trivial problem. To solve that we need to use aggregation framework, to select first record project, then unwind array to reduce nesting and last project2 to reconstruct document as required. match is used as a filter to reuduce amount of document in process.

var match = {
    $match : {
        "_id" : "nex67"
    }
}

var project = {
    $project : {
        _id : 1,
        ownedpartsFirst : {
            $slice : ["$ownedparts", 1]
        }
    }
}
var unwind = {
    $unwind : "$ownedpartsFirst"
}
var project2 = {
    $project : {
        _id : 1,
        ownedparts :
        [{
                id : "$ownedpartsFirst.id",
                history : {
                    $slice : ["$ownedpartsFirst.history", -1]
                }
            }
        ]
    }
}

db.collectionName.aggregate([match, project, unwind, project2])

output:

{
    "_id" : "nex67",
    "ownedparts" : [ 
        {
            "id" : "tool1",
            "history" : [ 
                {
                    "time" : ISODate("2016-06-07T09:12:54.015Z"),
                    "value" : 240.0
                }
            ]
        }
    ]
}

answered Jun 7, 2016 at 21:45

profesor79

9,5033 gold badges36 silver badges53 bronze badges

1 Comment

damnputer Over a year ago

this works too, but i'm choosing the one that uses $arrayElemAt as answer because it seems more optimal.

JorgeFo · Accepted Answer · 2016-06-07 21:03:24Z

-1

try with this

db.users.find(  
         {"_id": "nex67"},
         {"ownedparts.$.history": { $slice: -1 }})

And you have a problem with this consult

 db.users.find(  
{
         "_id": "nex67",
         "ownedparts.id": "tool1"
},  { "ownedparts.history.$": 1 })

The simbol $ is between ownedparts and history to get the element from you array

 db.users.find(  
{
         "_id": "nex67",
         "ownedparts.id": "tool1"
},  { "ownedparts.$.history": 1 })

edited Jun 7, 2016 at 21:03

answered Jun 7, 2016 at 20:57

JorgeFo

6111 gold badge6 silver badges12 bronze badges

1 Comment

profesor79 Over a year ago

this returns all sub array elements

Collectives™ on Stack Overflow

Using $slice when finding data from subdocument's array

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related