MongoDB aggregation framework with $and[$or[]]-CodePudding

I have a Mongo collection which looks like this

[
  {
    "city_town": "Melbourne",
    "visit_date": "2022-06-22T14:00:00.000Z",
    "payment_cleared_at": "2022-06-22T14:00:00.000Z"
  },
  {
    "city_town": "Perth",
    "visit_date": "2022-06-22T14:00:00.000Z",
    "payment_cleared_at": "2022-06-22T14:00:00.000Z"
  },
  {
    "city_town": "Perth",
    "visit_date": "2022-06-22T14:00:00.000Z",
    "payment_cleared_at": "2022-06-22T14:00:00.000Z"
  },
  {
    "city_town": "Perth",
    "visit_date": "2022-06-22T14:00:00.000Z",
    "payment_cleared_at": "2022-06-22T14:00:00.000Z"
  },
  {
    "city_town": "Perth",
    "visit_date": "2022-06-22T14:00:00.000Z",
    "payment_cleared_at": "2022-06-22T14:00:00.000Z"
  }
]

I want to do aggregation on the collection to sort by subtracting two date fields. This is what I am doing right now

db.collection.aggregate([
  {
    $match: {
      "$and": [
        {
          "$or": [
            {
              "city_town": "Melbourne",
              "visit_date": {
                "$gte": "2022-06-22T14:00:00.000Z",
                "$lte": "2022-06-23T13:59:59.999Z"
              }
            },
            {
              "city_town": "Perth",
              "visit_date": {
                "$gte": "2022-06-22T14:30:00.000Z",
                "$lte": "2022-06-23T14:29:59.999Z"
              }
            }
          ]
        },
        {
          "$or": [
            {
              "city_town": "Melbourne",
              "payment_cleared_at": {
                "$gte": "2022-06-14T14:00:00.000Z",
                "$lte": "2022-06-15T13:59:59.999Z"
              }
            },
            {
              "city_town": "Perth",
              "payment_cleared_at": {
                "$gte": "2022-06-14T14:30:00.000Z",
                "$lte": "2022-06-15T14:29:59.999Z"
              }
            }
          ]
        }
      ]
    }
  },
  {
    $project: {
      "time_diff": {
        "$subtract": [
          "$visit_date",
          "$payment_cleared_at"
        ]
      }
    }
  },
  {
    $sort: {
      "visit_date": 1,
      "time_diff": -1
    }
  },
  {
    $skip: 10
  },
  {
    $limit: 10
  }
])

It doesn't work since it has an $and. Is there a way to achieve the same result?

CodePudding user response：

Following query works. The second $or case doesn't give you any value, thats why $and doesn't work. I changed the value and time diff.

db.collection.aggregate([
  {
    $match: {
      "$and": [
        {
          "$or": [
            {
              "city_town": "Melbourne",
              "visit_date": {
                "$gte": "2022-06-22T14:00:00.000Z",
                "$lte": "2022-06-23T13:59:59.999Z"
              }
            },
            {
              "city_town": "Perth",
              "visit_date": {
                "$gte": "2022-06-22T14:30:00.000Z",
                "$lte": "2022-06-23T14:29:59.999Z"
              }
            }
          ]
        },
        {
          "$or": [
            {
              "city_town": "Melbourne",
              "payment_cleared_at": {
                "$gte": "2022-06-21T14:00:00.000Z",
                "$lte": "2022-06-23T13:59:59.999Z"
              }
            },
            {
              "city_town": "Perth",
              "payment_cleared_at": {
                "$gte": "2022-06-14T14:30:00.000Z",
                "$lte": "2022-06-15T14:29:59.999Z"
              }
            }
          ]
        }
      ]
    }
  },
  {
    $set: {
      "time_diff": {
        "$subtract": [
          {
            $toDate: "$visit_date"
          },
          {
            $toDate: "$payment_cleared_at"
          }
        ]
      }
    }
  },
  {
    $sort: {
      "visit_date": 1,
      "time_diff": -1
    }
  },
  {
    $skip: 0
  },
  {
    $limit: 10
  }
])

Working Mongo playground