Mongodb aggregation $project get array position element field value

Question

Document:

{
    "_id" : ObjectId("560dcd15491a065d6ab1085c"),
    "title" : "example title",
    "views" : 1,
    "messages" : [
        {
            "authorId" : ObjectId("560c24b853b558856ef193a3"),
            "authorName" : "Karl Morrison",
            "created" : ISODate("2015-10-02T00:17:25.119Z"),
            "message" : "example message"
        }
    ]
}

Project:

$project: {
    _id: 1,
    title: 1,
    views: 1,
    updated: '$messages[$messages.length-1].created' // <--- ReferenceError: $messages is not defined
}

I am trying to get the last elements created value from the array inside of the document. I was reading the documentation but this specific task has fallen short.

I've learnt it has to do with dot notation. However doesn't state how to get the last element.

Blakes Seven · Accepted Answer · 2015-10-02 01:13:22Z

5

You cannot just extract properties or basically change the result from a basic .find() query beyond simple top level field selection as it simply is not supported. For more advanced manipulation you can use the aggregation framework.

However, without even touching .aggregate() the $slice projection operator gets you most of the way there:

db.collection.find({},{ "messages": { "$slice": -1 } })

You cannot alter the structure, but it is the last array element with little effort.

Until a new release ( as of writing ) for MongoDB, the aggregation framework is still going to need to $unwind the array in order to get at the "last" element, which you can select with the $last grouping accumulator:

db.collection.aggregate([
    { "$unwind": "$messages" },
    { "$group": {
        "_id": "$_id",
        "title": { "$last": "$title" },
        "views": { "$last": "$views" },
        "created": { "$last": "$messages.created" }
   }}
])

Future releases have $slice and $arrayElemAt in aggregation which can handle this directly. But you would also need to set a variable with $let to address the dot notated field:

    [
        { "$project": {
            "name": 1,
            "views": 1,
            "created": {
                "$let": {
                    "vars": {
                        "message": { 
                            "$arrayElemAt": [
                                { "$slice": [ "$messages", -1 ] },
                                0
                            ]
                        }
                    },
                    "in": "$$message.created"
                }
            }
        }}
    ]

edited Oct 2, 2015 at 1:13

answered Oct 2, 2015 at 0:46

Blakes Seven

50.5k14 gold badges132 silver badges136 bronze badges

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

11 Comments

Blakes Seven Over a year ago

@KarlMorrison Yes I can read. But it's also pretty clear it may not be the best option, which is why there is also an alternate solution suggested.

basickarl Over a year ago

I like how you informed me of the unwind, however I do (unfortunately) need to manipulate the document further later on!

Blakes Seven Over a year ago

@KarlMorrison What you need to understand here is that your question asks for a field property from the array and from the last member of the array. This is what the answer tells you to do. If you want to keep copies of things then either copy the array field beforehand or $push back content to the array. There is no other way to presently do this. The next version of MongoDB will include a $slice operator for use with the aggregation framework. You also only get to ask the question you asked, and no other questions in a single question.

basickarl Over a year ago

I'm trying to wrap my head around your answer, I'll get back to you :)

Blakes Seven Over a year ago

@KarlMorrison If you have other aggregation operations then you really have no choice. Naturally, processing $unwind and needing to $group back the document is a good deal of overhead. Better to ask additional questions if you have broader details.

|

Collectives™ on Stack Overflow

Mongodb aggregation $project get array position element field value

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related