Mongodb aggregate (problem with $and

I have a problem with mongodb

const getBookingStatus = (status) => {
  if (!status) return {};
  const qStatus = {};
  if (status === 'progress') qStatus.$and = [{ $ne: statusList.declined }, { $lt: statusList.confirmed }];
  if (status === 'confirmed') qStatus.$and = [{ $gt: statusList.signed }, { $lt: statusList.completed }];
  if (status === 'done') qStatus.$gte = statusList.completed;
  return { status: { ...qStatus } };
};

When I call const query = ...getBookingStatus(status), and use it in a $match it works for the status === 'done' but doesn't for the others. I have this error : "message": "unknown operator: $and". I've tried to add $expr like : if (status === 'progress') qStatus.$expr = { $and: [{ $ne: statusList.declined }, { $lt: statusList.confirmed }] }; but it doesn't work neither. Please help me

CodePudding user response：

The problem has to do with how the top level status field factors into the the comparisons that get nested inside of the $and. When using $and, each entry is an expression that must reference a field. So right now what gets generated is something like:

{
  status:  {
    $and: [
      { $ne: <Val1> }, 
      { $lt: <Val2> }
    ] 
  }
}

If you remove the $and and the array wrappers, and just provide the two filters as a single object, you will generate something like this instead:

{
  status:  {
    $ne: <Val1>, 
    $lt: <Val2>
  }
}

This more closely matches what your 'done' situation generates and successfully uses:

{
  status:  {
    $gte: <Val1>
  }
}

Here is a basic playground demonstration.

So the solution is probably to change

  if (status === 'progress') qStatus.$and = [{ $ne: statusList.declined }, { $lt: statusList.confirmed }];
  if (status === 'confirmed') qStatus.$and = [{ $gt: statusList.signed }, { $lt: statusList.completed }];

To something like:

  if (status === 'progress') qStatus = { $ne: statusList.declined, $lt: statusList.confirmed };
  if (status === 'confirmed') qStatus = { $gt: statusList.signed, $lt: statusList.completed };