BSON 日期类型的 MongoDB 查询和聚合 [英] MongoDB query and aggregation for BSON date type

问题描述

我正在处理一个项目，该项目需要在一定时间后使未接受的订单到期.以下是订单的示例结构:

<代码>{_id:对象 ID，时间:整数历史: {created_at:日期，Accepted_at: ['Null', 'Date'],Completed_at: ['Null', 'Date']}}

如果 accepted_at 字段在 created_at 几个小时后仍然为空，则订单将被视为已过期.

如果订单被接受并且在 created_at 的 time 小时后 completed_at 仍然为空，则订单将失败.

我想从文档中获取失败的订单.像这样:

db.orders.aggregate([{$匹配:{"history.accepted_at": { $exists: true },history.created_at":{$exists: 真，$lte: new Date((new Date()).getTime() - $time *3600 * 1000)}}}])

解决方案

我建议您为所有人存储时间戳值，这是最通用的解决方案 &对于此类查询也很容易操作

所以你的架构就像

<代码>{时间:{类型:数字，默认值:0},历史:{created_at:{类型:数字，默认值:0},接受_在:{类型:数字，默认值:0},完成时间:{类型:数字，默认值:0}}}

然后像这样写你的查询

db.collection.aggregate([{$addFields:{end_deadline":{$添加:["$history.created_at",{$乘法:["$time",3600000]}]},}},{$匹配:{history.accepted_at":{$exists: 真},history.completed_at":空，$expr:{$gte: [ISODate("2020-01-23T00:00:00.441Z"),$end_deadline"]}}}])

样本数据

<预><代码>[{时间":12，start_from":1579910400441，历史": {created_at":1579737600441，accepted_at":1579741200441}},{时间":24，start_from":1579932000441，历史": {created_at":1579737600441，accepted_at":1579739400441，已完成_at":1579935600441}},{时间":24，start_from":1578700800441，历史": {created_at":1578528000441，accepted_at":1578614400441}}]

示例响应

<预><代码>[{"_id": ObjectId("5a934e000102030405000000"),end_deadline":1.579780800441e+12，历史": {accepted_at":1.579741200441e+12，created_at":1.579737600441e+12},start_from":1.579910400441e+12，时间":12},{"_id": ObjectId("5a934e000102030405000002"),end_deadline":1.578614400441e+12，历史": {accepted_at":1.578614400441e+12，created_at":1.578528000441e+12},start_from":1.578700800441e+12，时间":24}]

I am working on a project which needs to expire unaccepted orders after a set amount of time. Here is the sample structure of an order:

{
  _id: ObjectId,
  time: int
  history: {
    created_at: Date,
    accepted_at: ['Null', 'Date'],
    completed_at: ['Null', 'Date']
  }
}

If accepted_at field is still null after some hours from created_at, the order will be considered as expired.

If order is accepted and completed_at is still null after time hours from created_at the order will be failed.

I want to get failed orders from document. Something like this:

db.orders.aggregate([
  {
    $match: {
      "history.accepted_at": { $exists: true },
      "history.created_at" : {
         $exists: true,
         $lte: new Date((new Date()).getTime() - $time *3600 * 1000)
      }
    }
  }
])

解决方案

I would suggest you to store values of timestamps for all, that is most universal solution & also easy to manipulate for queries of such kind

So your schema be like

{
    time:{
        type: Number,
        default: 0
    },
    history:{
        created_at:{
            type: Number,
            default: 0
        },
        accepted_at:{
            type: Number,
            default: 0
        },
        completed_at:{
            type: Number,
            default: 0
        }
    }
}

And then write your query like this

db.collection.aggregate([
  {
    $addFields: {
      "end_deadline": {
        $add: [
          "$history.created_at",
          {
            $multiply: [
              "$time",
              3600000
            ]
          }
        ]
      },

    }
  },
  {
    $match: {
      "history.accepted_at": {
        $exists: true
      },
      "history.completed_at": null,
      $expr: {
        $gte: [
          ISODate("2020-01-23T00:00:00.441Z"),
          "$end_deadline"
        ]
      }
    }
  }
])

Sample Data

[
  {
    "time": 12,
    "start_from": 1579910400441,
    "history": {
      "created_at": 1579737600441,
      "accepted_at": 1579741200441
    }
  },
  {
    "time": 24,
    "start_from": 1579932000441,
    "history": {
      "created_at": 1579737600441,
      "accepted_at": 1579739400441,
      "completed_at": 1579935600441
    }
  },
  {
    "time": 24,
    "start_from": 1578700800441,
    "history": {
      "created_at": 1578528000441,
      "accepted_at": 1578614400441
    }
  }
]

Sample response

[
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "end_deadline": 1.579780800441e+12,
    "history": {
      "accepted_at": 1.579741200441e+12,
      "created_at": 1.579737600441e+12
    },
    "start_from": 1.579910400441e+12,
    "time": 12
  },
  {
    "_id": ObjectId("5a934e000102030405000002"),
    "end_deadline": 1.578614400441e+12,
    "history": {
      "accepted_at": 1.578614400441e+12,
      "created_at": 1.578528000441e+12
    },
    "start_from": 1.578700800441e+12,
    "time": 24
  }
]

这篇关于BSON 日期类型的 MongoDB 查询和聚合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！