CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
MarcF
MarcF

Reputation: 3299

Select last value of array in sub document

I have the following document structure

{ 
    "_id" : 1, 
    "prices" : { 
        "100" : [
                5.67,
                .
                .
                1.7,    
            ], 
        "101" : [
                4.67,
                .
                .
                1.4,  
    }, 
    "v" : 2 
}

We need to get the last value from each of the fields "100" and "101", e.g 1.7 & 1.4, using mongodb v3.0.2. Currently we are loading the whole document and then getting the required values in the application. It has become quite a bottleneck as each document may be ~1MB, which for 100s of documents is adding up.

We are hoping there is a suitable feature of mongodb that will allow us to just retrieve the data we need from the document, possibly as a separate call for each price field, i.e one call for the price "100" key and second call for the price "101" key.

We have experimented using the $slice operator but this unfortunately cannot be used with exclusion fields, https://jira.mongodb.org/browse/SERVER-3378.

We have also seen the following post: How to combine both $slice and select returned keys operation in function update?

Which almost works, but the return value from the query:

db.account_articles.find({"_id" : ObjectId("1")}, { "prices.100" : { $slice: -1 }, "_id" : 1 })

is:

{ 
    "_id" : 1, 
    "prices" : { 
        "100" : [
                1.7    
            ], 
        "101" : [
                4.67,
                .
                .
                1.4,  
    }
}

i.e. it's 90% of the way there, we just need to be able to exclude the "101" field.

Are we missing something or is this not possible in monogodb?

Upvotes: 3

Views: 3392

Answers (2)

WasiF
WasiF

Reputation: 28939

You can check out this simple and lot easier solution.

Return latest record from subdocument in Mongodb

just get the last index of your array item and you are done.

Upvotes: 1

Blakes Seven
Blakes Seven

Reputation: 50436

You need the aggregation framework to do this, I think you really should change the stucture, but working with what you have:

Model.aggregate(
    [
       { "$match": { "prices.100": { "$exists": true } }},
       { "$unwind": "$prices.100" },
       { "$group": {
          "_id": "$_id",
          "price": { "$last": "$prices.100" }
       }},
       { "$project": { "_id": 0, "prices.100": "$price" } }
     ]
)

In future releases you will be able to just use the new $slice operator:

Model.aggregate(
    [
        { "$match": { "prices.100": { "$exists": true } } },
        { "$project": {
            "_id": 0,
            "prices.100": { "$slice": ["$prices.100",-1] }
        }}
    ]
)

In fact you can do "both" fields at the same time:

Model.aggregate(
    [
        { "$match": { "prices.100": { "$exists": true } } },
        { "$project": {
            "_id": 0,
            "prices.100": { "$slice": ["$prices.100",-1] },
            "prices.101": { "$slice": ["$prices.100",-1] }
        }}
    ]
)

And that is a lot better than processing with $unwind and $last to get the last element of the array when $group is applied to get the data back.

It basically has the same performance as a regular query in the newer form. In the present form, it's going to be slower.

Upvotes: 3

Related Questions