CODEWITHSUNDEEP
mongodbnosql
michelem
michelem

Reputation: 14590

MongoDB How to compare fields in associative array

I'm looking for the right query in MongoDB to compare two values inside associative arrays, I have documents like this:

{ 
    "_id" : ObjectId("5502cc4280ee2cd549dee9e8"), 
    "formats" : [
        {
            "name" : "first", 
            "prices" : [
                {
                    "futurePrice" : 5.49, 
                    "price" : 5.49
                }
            ]
        }, 
        {
            "name" : "second",
            "prices" : [
                {
                    "futurePrice" : 5.49, 
                    "price" : 5.49
                }
            ]
        }
    ]
}

I need to compare the futurePrice and price fields to find document that have at least one format with a futurePrice > price

I tried something like this:

Collection.find({'formats.prices': { $elemMatch: 'this.futurePrice > this.price' }}, ...

But it doesn't seem to work, any idea?

Upvotes: 1

Views: 609

Answers (1)

thegreenogre
thegreenogre

Reputation: 1579

You can do this with the aggregation framework using $cond operator:

db.Testing.aggregate([
    {
        '$unwind': '$formats'
    },
    {
        '$unwind': '$formats.prices'
    },
    {
        '$project': 
        {
            formats :1, 
            eq : { 
                $cond: [ { $gt: [ '$formats.prices.futurePrice', '$formats.prices.price' ] }, 1, 0 ]
            }
        }
    },
    { 
        $match: { eq: 1 } 
    },
    {
        '$group': 
        {
            '_id' : '$_id',
            'formats': 
                {
                    '$push': '$formats'
                }
            }
        }
])

EDIT:

As @pepkin88 mentioned in the comment you can use $where operator to get the same result. Although it doesn't take advantage of indexes and may impact performance.

db.Testing.find(function() { 
    for (i = 0; i < this.formats.length; i++) { 
        for (j = 0; j < this.formats[i].prices.length; j++) 
            return this.formats[i].prices[j].futurePrice > this.formats[i].prices[j].price
    }
})

Upvotes: 2

Related Questions