如何使用mongo聚合将字符串转换为日期? [英] How can convert string to date with mongo aggregation?

问题描述

在一个收藏夹中，我存储了这种文档

In a collection, I store this kind of document

{
    "_id" : 1,
    "created_at" : "2016/01/01 12:10:10",
    ...
}.
{
    "_id" : 2,
    "created_at" : "2016/01/04 12:10:10",
    ...
}

我想通过使用聚合管道来查找具有"creared_at"> 2016/01/01的文档.

I would like to find documents have "creared_at" > 2016/01/01 by using aggregation pipeline.

任何人都可以将"created_at"转换为日期的解决方案，以便可以进行汇总吗?

Anybody have solution to convert "created_at" to date so can conpare in aggregation?

推荐答案

如前所述，您需要首先更改架构，以使created_at字段保留日期对象，而不是当前情况的字符串，然后您可以使用 find() 方法或聚合框架.前者将是最简单的方法.

As you have mentioned, you need to first change your schema so that the created_at field holds date objects as opposed to string as is the current situation, then you can query your collection either using the find() method or the aggregation framework. The former would be the most simple approach.

要将created_at转换为日期字段，您需要迭代 运算符.

To convert created_at to date field, you would need to iterate the cursor returned by the find() method using the forEach() method, within the loop convert the created_at field to a Date object and then update the field using the $set operator.

充分利用 批量API 用于提供更好性能的批量更新，因为您将以大约1000的批量发送操作到服务器，这是因为您没有将每个请求发送到服务器，而是每1000个发送一次，从而提供了更好的性能要求.

Take advantage of using the Bulk API for bulk updates which offer better performance as you will be sending the operations to the server in batches of say 1000 which gives you a better performance as you are not sending every request to the server, just once in every 1000 requests.

以下内容演示了这种方法，第一个示例使用MongoDB版本>= 2.6 and < 3.2中可用的Bulk API.它更新所有 通过将created_at字段更改为日期字段

The following demonstrates this approach, the first example uses the Bulk API available in MongoDB versions >= 2.6 and < 3.2. It updates all the documents in the collection by changing the created_at fields to date fields:

var bulk = db.collection.initializeUnorderedBulkOp(),
    counter = 0;

db.collection.find({"created_at": {"$exists": true, "$type": 2 }}).forEach(function (doc) {
    var newDate = new Date(doc.created_at);
    bulk.find({ "_id": doc._id }).updateOne({ 
        "$set": { "created_at": newDate}
    });

    counter++;
    if (counter % 1000 == 0) {
        bulk.execute(); // Execute per 1000 operations and re-initialize every 1000 update statements
        bulk = db.collection.initializeUnorderedBulkOp();
    }
})
// Clean up remaining operations in queue
if (counter % 1000 != 0) { bulk.execute(); }

下一个示例适用于新的MongoDB版本3.2，此版本自

The next example applies to the new MongoDB version 3.2 which has since deprecated the Bulk API and provided a newer set of apis using bulkWrite():

var cursor = db.collection.find({"created_at": {"$exists": true, "$type": 2 }}),
    bulkOps = [];

cursor.forEach(function (doc) { 
    var newDate = new Date(doc.created_at);
    bulkOps.push(         
        { 
            "updateOne": { 
                "filter": { "_id": doc._id } ,              
                "update": { "$set": { "created_at": newDate } } 
            }         
        }           
    );   

    if (bulkOps.length === 1000) {
        db.collection.bulkWrite(bulkOps);
        bulkOps = [];
    }
});         

if (bulkOps.length > 0) { db.collection.bulkWrite(bulkOps); }

架构修改完成后，您可以查询集合中的日期:

Once the schema modification is complete, you can then query your collection for the date:

var dt = new Date("2016/01/01");
db.collection.find({ "created_at": { "$gt": dt } });

如果您希望使用聚合框架进行查询，请运行以下管道以获取所需的结果.它使用 $match 运算符，与 find() 方法:

And should you wish to query using the aggregation framework, run the following pipeline to get the desired result. It uses the $match operator, which is similar to the find() method:

var dt = new Date("2016/01/01");
db.collection.aggregate([
    {
        "$match": { "created_at": { "$gt": dt } }
    }
])

这篇关于如何使用mongo聚合将字符串转换为日期?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！