MongoDB - find result where value is null or less than X

Question

I have a collection in MongoDB with two dates that define whether or not something is current. So I have an "end_date" that can be null, or may have a time value. An item that current has a null end_date or a time in the future. My query looks like this:

program_enrollments.find( {"start_date":{"$lte":1376982000},"end_date":[null,{"$gte ":1376982000}],"client":"52002d02cc94a31a0f000000"}, [] ) 

This looks proper to me, but do I need a different approach? I don't want to have a boolean flag that says whether or not the dates are current if I can avoid it.

Answer 1

You are looking for the $or operation, which accepts an array of operations. It would be like so:

$or: [{end_date: null}, {end_date: {$gte: 1376982000}}] 

Your entire query would look like:

program_enrollments.find({   start_date: {$lte: 1376982000},   $or: [     {end_date: null},      {end_date: {$gte: 1376982000}}   ],   client:"52002d02cc94a31a0f000000" }) 

To scale, you will want to find a way to remove either the $gte and $lte operators. MongoDB cannot use the combination of the $lte and $gte on different attributes in the query.

Answer 2

The $or operator could be quite cumbersome at times, especially if you have other fields with multiple options, then you'd have to put all the $or's nested within an $and.

Another option could be to use the negation operator:

"end_date": {"$not": {"$lt":1376982000}}

This will give you the same results as

$or: [{end_date: null}, {end_date: {$gte: 1376982000}}]

And your query would look like this, which is somewhat cleaner IMO:

program_enrollments.find({   start_date: {$lte: 1376982000},   end_date: {$not: {$lt: 1376982000}},   client:"52002d02cc94a31a0f000000" })