BSON 日期类型的 MongoDB 查询和聚合

本文介绍了BSON 日期类型的 MongoDB 查询和聚合的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

{_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: 真},historypleted_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 }, "historypleted_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 } ]