匹配条件和数组的最新日期 [英] Match conditions and latest date from array

查看：73 发布时间：2020/5/11 0:07:35 mongodb aggregation-framework

本文介绍了匹配条件和数组的最新日期的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

db.chat.find().pretty().limit(3)
{
    "_id" : ObjectId("593921425ccc8150f35e7662"),
    "user1" : 1,
    "user2" : 2,
    "messages" : [
        {
            "sender" : 1,
            "datetime" : ISODate("2017-06-08T10:04:50Z"),
            "body" : "hiii 0"
        },
        {
            "sender" : 2,
            "datetime" : ISODate("2017-06-09T10:04:50Z"),
            "body" : "hiii 1"
        },
        {
            "sender" : 2,
            "datetime" : ISODate("2017-06-10T10:04:50Z"),
            "body" : "hiii 2"
        }
    ]
}
{
    "_id" : ObjectId("593921425ccc8150f35e7663"),
    "user1" : 1,
    "user2" : 3,
    "messages" : [
        {
            "sender" : 1,
            "datetime" : ISODate("2017-06-08T10:04:50Z"),
            "body" : "hiii 0"
        },
        {
            "sender" : 3,
            "datetime" : ISODate("2017-06-09T10:04:50Z"),
            "body" : "hiii 1"
        },
        {
            "sender" : 1,
            "datetime" : ISODate("2017-06-10T10:04:50Z"),
            "body" : "hiii 2"
        }
    ]
}
{
    "_id" : ObjectId("593921425ccc8150f35e7664"),
    "user1" : 1,
    "user2" : 4,
    "messages" : [
        {
            "sender" : 1,
            "datetime" : ISODate("2017-06-08T10:04:50Z"),
            "body" : "hiii 0"
        },
        {
            "sender" : 1,
            "datetime" : ISODate("2017-06-09T10:04:50Z"),
            "body" : "hiii 1"
        },
        {
            "sender" : 4,
            "datetime" : ISODate("2017-06-10T10:04:50Z"),
            "body" : "hiii 2"
        }
    ]
}

在mongodb集合存储上，user1和user2之间进行了聊天. 我正在寻找一个查询，该查询将为我提供最新消息的结果，其中 message.sender =每行1条.

Above mongodb collection store chats between user1 and user2. I am looking for a query that will give me result of the latest messages where message.sender = 1 of each row.

即寻找3行作为输出

For user1=1 and user2, message hiii0 should only come
For user1=1 and user3, message hiii2 should only come
For user1=1 and user4, message hiii1 should only come

即.只有三行.

db.chat.find({"messages.sender":1})给出所有行，而我只查找具有最新日期时间的匹配行. 请帮助

db.chat.find({"messages.sender":1}) is giving all the rows while i am looking for just the matched row with the latest datetime. Please help

示例:

db.chat.aggregate([
  {$unwind:"$messages"},
  {$match:{"messages.sender":1}},
  {$sort:{"messages.datetime":-1}
])

给出的输出为

{ "_id" : ObjectId("593921425ccc8150f35e7663"), "user1" : 1, "user2" : 3, "messages" : { "sender" : 1, "datetime" : ISODate("2017-06-10T10:04:50Z"), "body" : "hiii 2" } }
{ "_id" : ObjectId("593921425ccc8150f35e7664"), "user1" : 1, "user2" : 4, "messages" : { "sender" : 1, "datetime" : ISODate("2017-06-09T10:04:50Z"), "body" : "hiii 1" } }
{ "_id" : ObjectId("593921425ccc8150f35e7662"), "user1" : 1, "user2" : 2, "messages" : { "sender" : 1, "datetime" : ISODate("2017-06-08T10:04:50Z"), "body" : "hiii 0" } }
{ "_id" : ObjectId("593921425ccc8150f35e7663"), "user1" : 1, "user2" : 3, "messages" : { "sender" : 1, "datetime" : ISODate("2017-06-08T10:04:50Z"), "body" : "hiii 0" } }
{ "_id" : ObjectId("593921425ccc8150f35e7664"), "user1" : 1, "user2" : 4, "messages" : { "sender" : 1, "datetime" : ISODate("2017-06-08T10:04:50Z"), "body" : "hiii 0" } }

最后两行是不可取的，因为它不是user1-user2记录的最新记录.

Last two rows are not desirable as its its not the latest record for user1-user2 record.

如果我要添加，{$ limit:1}，则只给出一行.

If i am adding ,{$limit:1} , its giving just one row.

MongoDB 3.4及更高版本

db.chat.aggregate([
  { "$match": { "messages.sender": 1 } },
  { "$replaceRoot": {
    "newRoot": {
      "$let": {
        "vars": {
          "messages": {
            "$filter": {
              "input": "$messages",
              "as": "m",
              "cond": { "$eq": [ "$$m.sender", 1 ] }
            }
          },
          "maxDate": {
            "$max": {
              "$map": {
                "input": {
                  "$filter": {
                    "input": "$messages",
                    "as": "m",
                    "cond": { "$eq": [ "$$m.sender", 1 ] }
                  }
                },
                "as": "m",
                "in": "$$m.datetime"
              }
            }
          }
        },
        "in": {
          "_id": "$_id",
          "user1": "$user1",
          "user2": "$user2",
          "messages": {
            "$arrayElemAt": [
              { "$filter": {
                "input": "$$messages",
                "as": "m",
                "cond": { "$eq": [ "$$m.datetime", "$$maxDate" ] }
              }},
              0
            ]
          }    
        }
      }
    }
  }}
])

这是利用 $replaceRoot ，它允许我们使用 $let .这里的主要优点是，这仅需要两个"流水线阶段.

This is the most efficient way which takes advantage of $replaceRoot which allows us to declare variables to use in the "replaced" structure using $let. The main advantage here is that this requires only "two" pipeline stages.

为了匹配数组内容，请使用 $filter 在其中应用 $eq 逻辑操作进行测试"sender"的值.条件匹配的地方，则仅返回匹配的数组条目.

In order to match the array content you use $filter where you apply the $eq logical operation to test the value of "sender". Where the condition matches, then only the matching array entries are returned.

使用相同的 $filter ，以便仅考虑匹配的发件人"条目，然后我们要应用 $max 中的已过滤"列表中的值，将其替换为"datetime"中的值. $max] 5 值是最新"日期根据条件.

Using the same $filter so that only the matching "sender" entries are considered, we then want to apply $max over the "filtered" list to the values in "datetime". The $max]5 value is the "latest" date by the conditions.

我们需要此值，以便稍后可以将过滤"数组的返回结果与此"maxDate"进行比较.这是在 $let 先前为过滤后的内容声明的两个变量"和"maxDate"再次应用于

We want this value so we can later compare the returned results from the "filtered" array to this "maxDate". Which is what happens inside the "in" block of $let where the two "variables" declared earlier for the filtered content and the "maxDate" are again applied to $filter in order to return what should be the only value that met both conditions having the "latest date" as well.

由于您只希望获得一个"结果，因此我们使用 $arrayElemAt 使用值而不是数组.

Since you only want "one" result, we use $arrayElemAt to use the value rather than the array.

db.chat.aggregate([
  { "$match": { "messages.sender": 1 } },
  { "$project": {
    "user1": 1,
    "user2": 1,
    "messages": {
      "$filter": {
        "input": "$messages",
        "as": "m",
        "cond": { "$eq": [ "$$m.sender", 1 ] }
      }
    },
    "maxDate": {
      "$max": {
        "$map": {
          "input": {
            "$filter": {
              "input": "$messages",
              "as": "m",
              "cond": { "$eq": [ "$$m.sender", 1 ] }
            }
          },
          "as": "m",
          "in": "$$m.datetime"
        }
      }
    }         
  }},
  { "$project": {
    "user1": 1,
    "user2": 1,
    "messages": {
      "$arrayElemAt":[
       { "$filter": {
         "input": "$messages",
          "as": "m",
          "cond": { "$eq": [ "$$m.datetime", "$maxDate" ] }
       }},
       0
      ]
    }
  }}
])

与上述基本相同，但是没有管道阶段，我们需要在两个 $project中应用阶段.原因是我们需要"maxDate"中的计算值"才能完成最终的 $filter ，并且无法在复合语句中执行此操作，因此我们拆分了管道.这对运营的总体成本影响很小.

This is basically the same process as described, but without the $replaceRoot pipeline stage, we need to apply in two $project stages. The reason for this is we need the "calculated value" from "maxDate" in order to do that final $filter, and it is not available to do in a compound statement, so instead we split the pipelines. This has a small impact on the overall cost of the operation.

在MongoDB 2.6到3.0中，除了 $arrayElemAt 并通过一个条目接受数组"结果，或将其放入 $unwind 阶段来处理现在应该是单个条目的事件.

In MongoDB 2.6 to 3.0 we can use most of the technique here except for $arrayElemAt and either accept the "array" result with a single entry or put in an $unwind stage to deal with what should now be a single entry.

db.chat.aggregate([
  { "$match": { "messages.sender": 1 } },
  { "$unwind": "$messages" },
  { "$match": { "messages.sender": 1 } },
  { "$sort": { "_id": 1, "messages.datetime": -1 } },
  { "$group": {
    "_id": "$_id",
    "user1": { "$first": "$user1" },
    "user2": { "$first": "$user2" },
    "messages": { "$first": "$messages" }
  }}
])

虽然看起来很简短，但这是迄今为止最昂贵的操作.在这里，您必须使用 $unwind 才能应用条件到数组元素.这是一个非常昂贵的过程，因为它会为每个数组条目生成每个文档的副本，并且基本上由现代运算符代替，这种现代运算符在过滤"的情况下避免了这种情况.

Whilst it looks brief, this is by far the most costly operation. Here you must use $unwind in order to apply the conditions to the array elements. This is a very costly process as it produces a copy of each document for each array entry, and is essentially replaced by the modern operators that avoid this in the case of "filtering".

第二个 $match 阶段此处丢弃任何元素(现在为文档")不符合发件人"条件.然后，我们使用 $sort 来放置"_id在每个文档的最前面加上最新"日期，因此有两个排序"键.

The second $match stage here discards any elements ( now "documents" ) which did not match the "sender" condition. Then we apply a $sort in order to put the "latest" date on top for each document by the _id, hence the two "sort" keys.

最后，我们仅应用 $group 参考原始文档，使用 $first 作为累加器以获得位于顶部"的元素.

Finally we apply $group in order to just refer to the original document, using $first as the accumulator to get the element that is "on top".

这篇关于匹配条件和数组的最新日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文

匹配条件和数组的最新日期 [英] Match conditions and latest date from array

问题描述

推荐答案

MongoDB 3.4及更高版本

相关文章

其他开发最新文章

热门教程

热门工具

登录关闭

匹配条件和数组的最新日期 [英] Match conditions and latest date from array

问题描述

推荐答案

MongoDB 3.4及更高版本

相关文章

其他开发最新文章

热门教程

热门工具

登录 关闭

登录关闭