How can I aggregate using a match on two different fields?

Question

This seems simple enough, but for some reason, when I put the second field in my match statement, my aggregation returns an empty array.

I have checked these :

Combining match on text field with aggregate

Match Two different fields in Mongoose, Aggregate?

Mongodb compare two fields with matched fields value aggregate

This is what I want :

I want a match statement that compares two fields from my schema that only return data for the createdByUserId and greater than or equal to some start date

$match: 
{ $and: 
  [ 
    { createdByUserId: userId }, 
    { dateStart: { $gte: new Date(startDate) } } 
  ] 
}

this is what it resolves out to be:

{
  "$match": {
    "$and": [
      {
        "createdByUserId": "5e6c2dddad72870c84f8476b"
      },
      {
        "dateStart": {
          "$gte": "2020-04-10T14:35:02.478Z"
        }
      }
    ]
  }
}

THIS IS WHAT I TRIED

I tried to simplify the problem down just using createdByUserId -but- that is not working

I believe the user ID match is the crux of my problem

$match: 
{
    createdByUserId: userId
}

resolved out to be:

{
  "$match": {
    "createdByUserId": "5e6c2dddad72870c84f8476b"
  }
}

I thought it might be a 'string to object id' issue and tried this (but it didn't work)

$match: 
{
    createdByUserId: { $eq: {$toOjbectId:userId} }
}

Resolved out to be:

{
  "$match": {
    "createdByUserId": {
      "$eq": {
        "$toOjbectId": "5e6c2dddad72870c84f8476b"
      }
    }
  }
}

More info

To show the entire pipeline, here is my aggregation:

var match = {
    $match: 
    { $and: 
        [ 
            { createdByUserId: userId }, 
            { dateStart: { $gte: new Date(startDate) } } 
        ] 
    }
};

var lookup ={
    $lookup:
    {
        from: "MyEvents",
        localField: "_id",
        foreignField: "eventId",
        as: "SignedUpUsers"
    }
};

var project = {
    $project: {
        "_id": 1,
        "name": 1,
        "dateStart": 1,
        "createdByUserId": 1
    }
};

var agg = [
    match,
    lookup,
    project
];

Event.aggregate(agg)
.sort({dateStart: 1})
.then( events => {
    if(events){
        return resolve(events);
    }else{
        return reject(null);
    }
})
.catch(err => {
    return reject(err);
})

Here is the Event Schema

const EventSchema = new Schema({
    name: {type: String, required: true},
    dateStart: {type: Date, required: true},
    createdByUserId: {type: Schema.Types.ObjectId, required: true},
})

Here is MyEvents schema

const MyEventsSchema = new Schema({
    userId: {type: Schema.Types.ObjectId, required: true},
    eventId: {type: Schema.Types.ObjectId, required: true},
})

( there is data in the collection that should be returned )

How can I match on two different fields ?

UPDATE

Thanks to whoami, this works:

const mongoose = require("mongoose");

 {
   $match: {
     createdByUserId: mongoose.Types.ObjectId(userId);
   }
 }

But can anybody tell me why this doesn't change the string to an object id:

{$toOjbectId:userId}

Answer 1

Yes the issue should be with input is of type string Vs DB field is of type ObjectId().Three ways to do it :

One Way : Change in aggregation query

 {
    $match: {
      $expr: {
        $eq: [
          userId,
          {
            $toString: "$createdByUserId" // Convert existing 'ObjectId()' to String
          }
        ]
      }
    }
  }

Test : MongoDB-Playground

Second Way : Convert string to ObjectId() in mongoose :

const mongoose = require("mongoose");

    {
      $match: {
        createdByUserId: mongoose.Types.ObjectId(userId);
      }
    }

Third way :

db.collection.aggregate([
  {
    $match: {
      $expr: {
        $eq: [
          {
            $toObjectId: "5e6c2dddad72870c84f8476b" /** Convert input to ObjectId() using aggregation */
          },
          "$createdByUserId"
        ]
      }
    }
  }
])

Test : MongoDB-Playground

Issue with your query : As $ObjectId is an aggregation operator - You need to use aggregation syntax in $match.

I would prefer second process as it might have better performance & use an index on createdByUserId field. Anyway you can always use explain to test your query performance.

Answer 2

You can Follow This Code

$match: {
    $and: [
      {
        "createdByUserId": "5e6c2dddad72870c84f8476b"
      },
      {
        "dateStart": {
          $gte: "2020-04-10T14:35:02.478Z"
        }
      }
    ]
  }