帶有$and[$or[]]的MongoDB聚合框架

我有一個看起來像這樣的 Mongo 收藏

[
  {
    "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"
  }
]

我想對集合進行聚合以通過減去兩個日期欄位進行排序。這就是我現在正在做的

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
  }
])

它不起作用，因為它有一個 $and。有沒有辦法達到相同的結果？

uj5u.com熱心網友回復：

以下查詢有效。第二種$or情況沒有給你任何價值，這就是為什么$and不起作用。我更改了值和時間差異。

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
  }
])

作業Mongo游樂場

標籤：mongodb 聚合框架

上一篇：檢查Jinja模板中的變數是否已更改的最佳方法？

下一篇：MongoDB：按性別、civilStatus和ProfessionalCategory對用戶進行分組和計數