comparing two fields from the same document

[geoffeg]

I know there is no way to compare two fields from the same document in MongoDB 2.0:

db.foo.save({

    "_id" : ObjectId("5037f1d19cb590b9b72a2558"),

    "update-date" : ISODate("1970-01-01T00:00:00Z"),

    "read-date" : ISODate("2012-08-24T21:27:37.070Z")

})

db.foo.find({"read-date' : { $gt : "update-date" }})

Is there some way to do this in 2.2 with the new aggregation framework? Dates or integers (epoch for instance) would be fine.

Thanks,

Geoff

[lwb]

Hi Geoff, 

Yes, you will be able to do this with the new aggregation framework in 2.2, which should be released in the next few weeks. In this framework, you'll be able to compare fields from the same document using the pipeline operator $match. The documentation is here: http://docs.mongodb.org/manual/reference/aggregation/ .  

[Gianfranco]

Hi Geoff,

There is a way to do it in JavaScript with the $where operator but it's using the language components to do this and not MongoDB's.

http://docs.mongodb.org/manual/reference/operator/where/

db.foo.find({ $where: "this.read-date > this.update-date" })

So if you have a lot to do a lot of queries like this is better to use the Aggregation Framework as Louisa said.

Gianfranco

On Friday, August 24, 2012 5:31:03 PM UTC-4, geoffeg wrote:

[Stephen Steneker]

Hi Geoff,

Using the aggregation framework, here is an example that will find documents that have an updateDate newer than the readDate:

        db.foo.aggregate(

                { $project: {

                        readDate: 1,

                        updateDate: 1,

                        isUpdated: { $gt:["$updateDate","$readDate"] }

                }},

                { $match : {

                        isUpdated : true,

                }}

        )

Computed fields (such as the `isUpdated` date comparison in this example) can be added in a $project or $group command, and filtered using $match.

Cheers,

Stephen