使用 $lte $gte 的 $fitler 嵌套数组 [英] $fitler nested array using $lte $gte

问题描述

我正在使用 Mongoose 和 MongoDB 实现一个带有用户"文档的后端，例如:

I am implementing a backend using Mongoose and MongoDB with "user" documents such as this:

> db.users.find().pretty()
{
        "_id" : ObjectId("5c46eb642c825626124b1e3c"),
        "username" : "admin",
        "searches" : [
                ISODate("2019-01-30T14:52:07Z"),
                ISODate("2019-01-30T14:53:40Z"),
                ISODate("2019-01-30T14:54:48Z"),
                ISODate("2019-02-03T17:11:57Z"),
                ISODate("2019-02-04T06:40:00Z")
        ]
}

搜索字段是一个数组，记录用户运行某些搜索功能的时间.我想选择一个用户名并计算在过去一小时、一天和总共运行的搜索次数.我的问题是在一个查询中获取这三个值.

The searches field is an array logging the time when the user run some search functionality. I want to select a username and compute how many searches were run in the last hour, day, and in total. My problem is getting these three values in one query.

我正在使用聚合来选择用户，使用展开提取搜索，按时查询，例如日期大于一小时前，并计算结果.

I am using aggregation to select the user, extract the searches with unwind, query on time e.g. dates greater than one hour ago, and count the results.

>let now = new Date()
ISODate("2019-02-04T06:56:29.095Z")
>let onehourago = new Date(now.getTime() - 1000 * 3600);
>let yesterday = new Date(now.getTime() - 1000 * 3600 * 24);
>let queryUser = { $match: {username: "admin"}};
>let unwind = { $unwind : "$searches" };
>let queryTime = { $match: {searches: {$gte: onehourago}}};
>let counter = {$count: "lasthour"};
>db.users.aggregate([queryUser, unwind, queryTime, counter])
{ "lasthour" : 1 }

我想得到:

{ "lasthour" : 1, "today": 2, "total": 5 }

如果在没有匹配项时查询返回 0 而不是空，则加分(但我可以在 javascript 中解决这个问题).

Bonus point if the query returns 0 instead of empty when there is no match (but I can work around that in javascript).

推荐答案

您可以使用 $filter 聚合过滤掉searches 数组

You can use $filter aggregation to filter out the searches array

db.collection.aggregate([
  { "$match": { "username": "admin" }},
  { "$project": {
    "total": { "$size": "$searches" },
    "lasthour": {
      "$size": {
        "$filter": {
          "input": "$searches",
          "as": "search",
          "cond": {
            "$and": [
              { "$gte": ["$$search", onehourago] },
              { "$lte": ["$$search", now] }
            ]
          }
        }
      }
    },
    "today": {
      "$size": {
        "$filter": {
          "input": "$searches",
          "as": "search",
          "cond": {
            "$and": [
              { "$gte": ["$$search", yesterday] },
              { "$lte": ["$$search", now] }
            ]
          }
        }
      }
    }
  }}
])

这篇关于使用 $lte $gte 的 $fitler 嵌套数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！