check if field exists in match query aggregation mongodb

Question

i have a mongodb collection of events from which i want to get the current availablae events so i am using match query like that

   Event.aggregate([ { $match: { isActive: true, ...matchQuery, startDate:{$lt:today}, endDate:{$gt:today} } },])

the problem is that not all the events in my database have startDate and enDate fields and i want to get those events too , so i want to check if startDate and endDate exists then they should be lt and gt than today if they don't exist and the document match the rest of the query , i want to get it . i tried this way but doesn't work

{ $match: { isEnabled: true, ...matchQuery, startDate ? {startDate:{$lt:today}}, endDate ?  endDate:{$gt:today} } },

thank you

Please add your sample data and query here mongoplayground.net and update the link in the strory. — Tushar Gupta - curioustushar
– Tushar Gupta - curioustushar, Commented Mar 30, 2021 at 11:10

Dheemanth Bhat · Accepted Answer · 2021-03-30 11:25:02Z

7

So the idea is to fetch all documents where:

startDate AND endDate does not exits.

OR

today is between the range startDate AND endDate.

Try this:

let today = new Date();

db.events.aggregate([
    {
        $match: {
            isActive: true,
            $or: [
                {
                    startDate: { $exists: false },
                    endDate: { $exists: false }
                },
                {
                    startDate: { $lt: today },
                    endDate: { $gt: today }
                }
            ]
        }
    }
])

Output:

{
    "_id" : ObjectId("..."),
    "isActive" : true,
    "startDate" : ISODate("2021-03-01T00:00:00.000+05:30"),
    "endDate" : ISODate("2021-03-31T00:00:00.000+05:30")
},
{
    "_id" : ObjectId("..."),
    "isActive" : true
}

Test data:

{
    "_id" : ObjectId("..."),
    "isActive" : true,
    "startDate" : ISODate("2021-03-01T00:00:00.000+05:30"),
    "endDate" : ISODate("2021-03-31T00:00:00.000+05:30")
},
{
    "_id" : ObjectId("..."),
    "isActive" : true,
    "endDate" : ISODate("2021-03-31T00:00:00.000+05:30")
},
{
    "_id" : ObjectId("..."),
    "isActive" : true,
    "startDate" : ISODate("2021-03-01T00:00:00.000+05:30")
},
{
    "_id" : ObjectId("..."),
    "isActive" : true
},
{
    "_id" : ObjectId("..."),
    "isActive" : true,
    "startDate" : ISODate("2021-02-01T00:00:00.000+05:30"),
    "endDate" : ISODate("2021-03-03T00:00:00.000+05:30")
}

edited Mar 30, 2021 at 11:25

answered Mar 30, 2021 at 11:18

Dheemanth Bhat

4,5022 gold badges30 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ghost Over a year ago

Thank you very much , your answer is logic and it helped me . Thank you

Epple Over a year ago

Why your aggregate query not using $expr in $match stage ?

Dheemanth Bhat Over a year ago

$expr is used to compare fields from the same document in a $match stage. In the above query the comparison is between the fields in a document (startDate, endDate) and external variable (today or a constant value false).

Dheemanth Bhat Over a year ago

Check: mongodb.com/docs/manual/reference/operator/query/expr/#behavior

Collectives™ on Stack Overflow

check if field exists in match query aggregation mongodb

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related