首页 > mongodb > 查询最后一个数组值

查询最后一个数组值 (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的主体和消息的最后一个cap ='B'。即输出应该是最后2行。

即可取的输出。

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

解决方法

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

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但基本相同。

首先查询选择文档,我们实际上不能说“仅”匹配数组的最后一个元素但我们想要过滤根本没有数组条件的文档。

$redact是查看“最后”数组条目并测试字段值的实际内容。我们只能从数组中注明该字段,$messages.capty只返回这些项的数组。在这里,我们接着$slice或者甚至$arrayElemAt想要得到最后一个值,作为索引-1

此时我们只“过滤”了与条件不符的“文件”。最后一个$project阶段采用数组的最后一个元素,检查它是否与条件匹配(它应该在前面的阶段),提取值"body"并将单个数组内容转换为普通值。

您可以交替放弃“谨慎”,只需抓住最后一个数组元素,因为$redact应该完成它的工作:

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

整个事情真的分解为“将可能的文档与查询匹配”,然后“比较并用$slice或提取最后一个元素$arrayElemAt”。

结果是:

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

问题

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

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.

i.e desireable output.

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

解决方法

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

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.

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.

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.

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".

Results are:

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