$和[$或[]]的MongoDB聚合框架

发布于 2025-02-10 00:28:39 字数 2157 浏览 0 评论 0原文

我有一个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
  }
])

因为它有$和。有没有办法实现相同的结果？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

毁虫ゝ 2025-02-17 00:28:39

以下查询作品。第二个$或案例没有任何值，这就是为什么$和不起作用的原因。我更改了价值和时间差异。

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 playground

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

回复收藏 0 原文

~没有更多了~