查询最后一个数组的值 [英] Query on Last Array Value

问题描述

db.chat.find().pretty().limit(3)
{
    "_id" : ObjectId("593921425ccc8150f35e7662"),
    "user1" : 1,
    "user2" : 2,
    "messages" : [
        {
            "capty" : 'A',
            "body" : "hiii 0"
        },
        {
            "capty" : 'B',
            "body" : "hiii 1"
        },
        {
            "capty" : 'A',
            "body" : "hiii 2"
        }
    ]
}
{
    "_id" : ObjectId("593921425ccc8150f35e7663"),
    "user1" : 1,
    "user2" : 3,
    "messages" : [
        {
            "capty" : 'A',
            "body" : "hiii 0"
        },
        {
            "capty" : 'A',
            "body" : "hiii 1"
        },
        {
            "capty" : 'B',
            "body" : "hiii 23"
        }
    ]
}
{
    "_id" : ObjectId("593921425ccc8150f35e7664"),
    "user1" : 1,
    "user2" : 4,
    "messages" : [
        {
            "capty" : 'A',
            "body" : "hiii 0"
        },
        {
            "capty" : 'B',
            "body" : "hiii 1"
        },
        {
            "capty" : 'B',
            "body" : "hiii 24"
        }
    ]
}

我不是要找出将给我提供user2列表的列表，以及其中user1 = 1且正文的最后capty ='B'的正文的查询.即输出应为最后两行.

I am not to figure out the query that will give me list of user2 and the body where user1=1 and last capty of messages = 'B'. i.e output should be last 2 rows.

即期望的输出.

user2:3, "body" : "hiii 23"
user2:4, "body" : "hiii 24"

推荐答案

这里的主要内容是聚合 $slice 从数组中获取最后一个元素，

The main thing here is the aggregation $slice to get the last element from the array,

db.chat.aggregate([
 { "$match": { "user1": 1,  "messages.capty": "B" } },
 { "$redact": {
   "$cond": {
     "if": { 
       "$eq": [ { "$slice": [ "$messages.capty", -1 ] }, ["B"] ]  
     },
     "then": "$$KEEP",
     "else": "$$PRUNE"
   }
 }},
 { "$project": {
   "user2": 1,
   "body": {
     "$arrayElemAt": [
       { "$map": {
         "input": { 
           "$filter": {
             "input": { "$slice": [ "$messages",-1 ] },
             "as": "m",
             "cond": { "$eq": [ "$$m.capty", "B" ] }
           }
         },
         "as": "m",
         "in": "$$m.body"
       }},
       0
     ]
   }
 }}
])

在 $project中，我实际上是特别安全" 阶段使用 $filter 基本相同.

I'm actually being "extra safe" in the $project stage with the $filter but it all basically the same.

首先查询选择文档，我们现在不能说是仅"匹配数组的最后一个元素，但是我们要过滤完全没有数组条件的文档.

First the query selects the documents, we cannot actually say at this point to "only" match the last element of the array but we want to filter documents that do not have the condition on the array at all.

$redact 是看起来真实的东西在最后一个"数组条目中，并测试该字段的值.我们不能用$messages.capty标记数组中的字段，该字段仅返回这些项目的数组.然后，我们在这里 $slice 甚至是

The $redact is the actual thing that looks at the "last" array entry and tests the value of the field. We can notate just the field from the array by $messages.capty which returns just an array of those items. Here we then $slice or even $arrayElemAt if you want to get the last value, being the index of -1.

这时，我们仅过滤"了不符合条件的文档".最后的 $project 阶段采用数组的最后一个元素，检查它是否符合条件(在较早的阶段应符合条件)，提取"body"的值，然后将单个数组的内容转换为纯值.

At this point we only "filtered" the "documents" which do not match the condition. The final $project stage takes the last element of the array, checks it matches the condition ( which it should by the earlier stages ), extracts the value of "body" and turns the single array content into just the plain value.

您可以选择放弃小心"，而只需抓取最后一个数组元素，因为$redact应该已经完成​​了它的工作:

You can alternately forego the "carefulness" and simply just grab the last array element since $redact should have done it's job:

db.chat.aggregate([
 { "$match": { "user1": 1,  "messages.capty": "B" } },
 { "$redact": {
   "$cond": {
     "if": { 
       "$eq": [ { "$arrayElemAt": [ "$messages.capty", -1 ] }, "B" ]  
     },
     "then": "$$KEEP",
     "else": "$$PRUNE"
   }
 }},
 { "$project": {
   "user2": 1,
   "body": {
     "$arrayElemAt": [ "$messages.body", -1 ]
   }
 }}
])

整个过程实际上是分解为使可能文档与查询匹配"，然后与

The whole thing really breaks down to "match the possible documents with a query" and then "compare and extract the last element with $slice or $arrayElemAt".

结果是:

{
        "_id" : ObjectId("593921425ccc8150f35e7663"),
        "user2" : 3,
        "body" : "hiii 23"
}
{
        "_id" : ObjectId("593921425ccc8150f35e7664"),
        "user2" : 4,
        "body" : "hiii 24"
}

这篇关于查询最后一个数组的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！