Mongodb获取子文档并按日期过滤

I have a collection with following documents.

我有一个包含以下文件的集合。

{
    "id":1,
    "url":"mysite.com",
    "views":
     [
       {"ip":"1.1.1.1","date":ISODate("2015-03-13T13:34:40.0Z")},
       {"ip":"2.2.2.2","date":ISODate("2015-03-13T13:34:40.0Z")},
       {"ip":"1.1.1.1","date":ISODate("2015-02-13T13:34:40.0Z")},
       {"ip":"1.1.1.1","date":ISODate("2015-02-13T13:34:40.0Z")}
     ]
},
{
    "id":2,
    "url":"mysite2.com",
    "views":
     [
       {"ip":"1.1.1.1","date":ISODate("2015-01-13T13:34:40.0Z")},
       {"ip":"2.2.2.2","date":ISODate("2015-01-13T13:34:40.0Z")},
       {"ip":"1.1.1.1","date":ISODate("2015-02-13T13:34:40.0Z")},
       {"ip":"1.1.1.1","date":ISODate("2015-02-13T13:34:40.0Z")}
     ]
}

How can I get documents where id = 1 and date(Y-m) = 2015-02 ?

如何获取id = 1和date(Y-m)= 2015-02的文档?

2 个解决方案

#1

You can use aggregation pipeline operators. The following will return all subdocuments in the views array that match your criteria.

您可以使用聚合管道运算符。以下将返回views数组中符合条件的所有子文档。

$match to filter select document with given id $unwind the views array. In your first $project stage use the $year operator to return the year for date and $month the month for the date. Next filter your documents by month and year using $match. $group your document by _id and use another $project stage to reshape them.

$ match to filter select document with given id $ unwind the views array。在您的第一个$项目阶段,使用$ year运算符返回日期的年份和日期的月份$ month。接下来使用$ match按月和年过滤您的文档。 $按_id对文档进行分组,并使用另一个$ project阶段对其进行重新整形。

db.collection.aggregate(
    [
        {'$match': {'id': 1}}, 
        {'$unwind': '$views'}, 
        {'$project': {
            'id': 1,
            'year': {'$year': '$views.date'}, 
            'month': {'$month': '$views.date'}, 
            'url': 1, 'views': 1
            }
        }, 
        {'$match': {'year': 2015, 'month': 2}},
        {'$group': {
                       '_id': '$id', 
                       'url': {'$first': 'url'}, 
                       'views': {'$push': '$views'}
                   }
        },
        {'$project': {'id': '$_id', 'views': 1, 'url': 1, '_id': 0}}
    ]
)

Result

{
    "url" : "url",
    "views" : [
            {
                    "ip" : "1.1.1.1",
                    "date" : ISODate("2015-02-13T13:34:40Z")
            },
            {
                    "ip" : "1.1.1.1",
                    "date" : ISODate("2015-02-13T13:34:40Z")
            }
    ],
    "id" : 1
}

#2

To get the specific "views" array element (subdocument) that satisfies the above criteria, use $elemMatch projection operator as follows:

要获取满足上述条件的特定“views”数组元素(子文档),请使用$ elemMatch投影运算符,如下所示:

var start = new Date(2015, 0, 31, 23, 59, 59),
    end = new Date(2015, 2, 1);
db.test.find(
{
    id: 1, 
    "views.date": { "$gt": start, "$lt": end}
}, 
{
    "id": 1,
    "views": { 
        "$elemMatch": { 
            "date": { "$gt": start, "$lt": end} 
        } 
    }
});

Returns:

/* 0 */
{
    "_id" : ObjectId("555089bf9cd8fa39c7971e33"),
    "id" : 1,
    "views" : [ 
        {
            "ip" : "1.1.1.1",
            "date" : ISODate("2015-02-13T13:34:40.000Z")
        }
    ]
}

If you want to return just the whole document then use the date range query without the $elemMatch projection as follows:

如果您只想返回整个文档,请使用不带$ elemMatch投影的日期范围查询,如下所示:

var start = new Date(2015, 0, 31, 23, 59, 59),
    end = new Date(2015, 2, 1);
db.test.find({id: 1, "views.date": { "$gt": start, "$lt": end}})

The query above will return the whole document which matched the above criteria, including as=ll the subdocuments:

上面的查询将返回符合上述条件的整个文档,包括as = ll子文档:

/* 0 */
{
    "_id" : ObjectId("5550866d9cd8fa39c7971e2e"),
    "id" : 1,
    "url" : "mysite.com",
    "views" : [ 
        {
            "ip" : "1.1.1.1",
            "date" : ISODate("2015-03-13T13:34:40.000Z")
        }, 
        {
            "ip" : "2.2.2.2",
            "date" : ISODate("2015-03-13T13:34:40.000Z")
        }, 
        {
            "ip" : "1.1.1.1",
            "date" : ISODate("2015-02-13T13:34:40.000Z")
        }, 
        {
            "ip" : "1.1.1.1",
            "date" : ISODate("2015-02-13T13:34:40.000Z")
        }
    ]
}

#1