MongoDB [NodeJS] find only documents with array elements in range

Question

I have documents with a history. I want to find documents with a special history entry. History-Element:

{
    name:"added",
    date: Isodate
}

The query should get all documents where name is "added" and the date is lower than a reference. In case of having a date that is higher, the document should be skipped. A document can have multiple "added" events

Here's my query, to find all documents with added history elements in the range of 7 days, if one element is lower than 7 days, the document should not be displayed:

 var expiryDate = new Date();
 expiryDate.setDate(expiryDate.getDate() - 7);

 var query = {
        $and: [
            {
                history: {
                    $elemMatch:{
                        event: "added",
                        date: {
                            $not: {
                                $gte: expiryDate.toISOString()
                            },
                        }
                    }
                }
            },
            {
                history: {
                    $elemMatch: {
                        event: "added",
                        date: {
                            $lt: expiryDate.toISOString()
                        }
                    }
                }
            }
        ]
    }

Here's the call:

self.db.items.find(query).toArray(function (err, items) { 
    if(items)
       console.log(items.length);
});

The problem is, that only the $lte part works. If there's a date greater than the reference it will be outputted though.

score 1 · Accepted Answer · answered Jan 20 '16 at 12:51

Consider an alternative such as the aggregation framework. Running the following pipeline would give you the desired result, with the history array filtered according the the given date criteria.

Note: There is no deed to cast the JavaScript Date object to an ISOString as Mongo wraps objects of Date type with the ISODate helper but internally the native JS Date objects are stored as a 64 bit integer representing the number of milliseconds since the Unix epoch (Jan 1, 1970), which results in a representable date range of about 290 millions years into the past and future.

var expiryDate = new Date();
expiryDate.setDate(expiryDate.getDate() - 7);

var pipeline = [
    {
        "$match": {
            "history.event": "added",
            "history.date": { "$lt": expiryDate }
        }
    },
    {
        "$project": {
            "other_fields": 1, 
            "history": {
                "$setDifference": [
                    {
                        "$map": {
                            "input": "$history",
                            "as": "el",
                            "in": {
                                "$cond": [
                                    {  
                                        "$and": [
                                            { "$eq": [ "$$el.event", "added" ]}
                                            { "$lt": [ "$$el.date", expiryDate ]}
                                        ]
                                    },
                                    "$$el",
                                    false
                                ]
                            }
                        }
                    },
                    [false]
                ]
            }
        }
    }
];

db.collection.aggregate(pipeline)

The above pipeline involves filtering the history array so that you remove the documents which do not satisfy the above given criteria. This is made possible through the $setDifference and $map operators.

The $map operator in essence creates a new array field that holds values as a result of the evaluated logic in a subexpression to each element of an array. The $setDifference operator then returns a set with elements that appear in the first set but not in the second set; i.e. performs a relative compliment of the second set relative to the first. In this case it will return the final history array that has elements not related to the parent documents via the date and event properties.

Tyvm for this, I'm right now testing it. I'll response as soon as I get it work :) — marcel, Jan 20 '16 at 14:52
It works. I have ISO-Strings as date in the database. Without .toISOString() I will not get any results unfortunately. — marcel, Jan 20 '16 at 14:56
This is partly the reason why your initial query isn't working. Doing comparisons with dates/timestamps is more efficient than string values and it is recommended to either store all your dates as Date objects in UTC, or, a consistent timezone possibly related to the local datacenter, and then convert your date values to the proper local timezone on the client. Since you have have ISO-Strings as date, it is the client-side responsibility to filter and process this value as mongo treats it like a string. You can convert to date with this [**answer**.](http://stackoverflow.com/a/34837902/122005) — chridam, Jan 20 '16 at 15:11

MongoDB [NodeJS] find only documents with array elements in range

1 Answers1