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

查看：12 发布时间：2022/1/20 13:58:51 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 行作为输出

i.e looking for 3 rows as output

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"进行比较.这是 "in" 块内发生的情况>$let 之前为过滤后的内容声明的两个变量"和maxDate"再次应用于 $filter 以返回满足最新日期"这两个条件的唯一值"也一样.

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
      ]
    }
  }}
])

这与描述的过程基本相同，但没有 $replaceRoot pipeline阶段，我们需要在两个$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 为了只引用原始文档，使用 $首先 作为累加器来获取在顶部"的元素.

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 及以上版本

相关文章

其他开发最新文章

热门教程

热门工具

登录 关闭

登录关闭