Search code examples
node.jsmongodbmongojs

Compare values inside same subdocument for findOne() [MongoDB]

I have a database full of objects that look ~exactly like this (simplified for clarity):

{
    "_id": "GIFT100",
    "price": 100,
    "priceHistory": [
        100, 110
    ],
    "update": 1444183299242
}

What I'm trying to do is create a query document for MongoJS (or MongoDB and I can figure out the rest) that looks for the fact that priceHistory[0] < priceHistory[1].

I would want my query document to return the above record as a result. Alternatively, I could change my document code to compare price < priceHistory[0] but I believe this still leads to the same problem (comparing values inside the same document).

Any help would be appreciated, I've exhausted my Google-foo.

Edit:

I want to return a set of records that indicate a price drop since our last scan (performed daily). Basically a set of "sale" items from a data source I don't control.

Solution

  • You can use the $where clause, but be careful--it's slow, it cannot use your indexes, and it will perform a full table scan. Pass on whatever Javascript you want to use for comparison:

    db.collection.findOne({$where: "priceHistory[0] < priceHistory[1]"})

    Additionally, you can skip the $where statement if that's the only thing you're querying by:

    db.collection.findOne("priceHistory[0] < priceHistory[1]")