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

问题描述

db.chat.find().pretty().limit(3){"_id" : ObjectId("593921425ccc8150f35e7662"),用户 1":1，用户 2":2，消息":[{"capty" : 'A',身体":hiii 0"},{"capty" : 'B',身体":hiii 1"},{"capty" : 'A',身体":hiii 2"}]}{"_id" : ObjectId("593921425ccc8150f35e7663"),用户 1":1，用户 2":3，消息":[{"capty" : 'A',身体":hiii 0"},{"capty" : 'A',身体":hiii 1"},{"capty" : 'B',身体":hiii 23"}]}{"_id" : ObjectId("593921425ccc8150f35e7664"),用户 1":1，用户 2":4，消息":[{"capty" : 'A',身体":hiii 0"},{"capty" : 'B',身体":hiii 1"},{"capty" : 'B',身体":hiii 24"}]}

我不打算找出可以为我提供 user2 列表和正文的查询，其中 user1=1 和 last capty of messages = 'B'.即输出应该是最后 2 行.

即理想的输出.

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

解决方案

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

db.chat.aggregate([{ "$match": { "user1": 1, "messages.capty": "B" } },{$redact":{$cond":{如果": {"$eq": [ { "$slice": [ "$messages.capty", -1 ] }, ["B"] ]},"then": "$$KEEP","else": "$$PRUNE"}}},{$项目":{用户 2":1，身体": {$arrayElemAt":[{$地图":{输入": {$过滤器":{"input": { "$slice": [ "$messages",-1 ] },"as": "m","cond": { "$eq": [ "$$m.capty", "B" ] }}},"as": "m","in": "$$m.body"}},0]}}}])

我实际上在 中非常安全"$project 阶段与 $过滤器但基本上都是一样的.

首先查询选择文档，此时我们实际上不能说仅"匹配数组的最后一个元素，但我们想过滤根本没有数组条件的文档.

$redact是查看最后一个"数组条目并测试字段值的实际内容.我们可以通过 $messages.capty 来标记数组中的字段，它只返回这些项目的数组.然后我们在这里 $slice 或甚至 $arrayElemAt 如果你愿意获取最后一个值，即 -1 的索引.

此时我们只过滤"了不符合条件的文档".最后的 $project 阶段需要数组的最后一个元素，检查它是否匹配条件(它应该由早期阶段)，提取 "body" 的值并将单个数组内容转换为纯值.>

你也可以放弃小心"，简单地获取最后一个数组元素，因为 $redact 应该已经完成​​了它的工作:

db.chat.aggregate([{ "$match": { "user1": 1, "messages.capty": "B" } },{$redact":{$cond":{如果": {"$eq": [ { "$arrayElemAt": [ "$messages.capty", -1 ] }, "B" ]},"then": "$$KEEP","else": "$$PRUNE"}}},{$项目":{用户 2":1，身体": {"$arrayElemAt": [ "$messages.body", -1 ]}}}])

整个事情真的分解为将可能的文档与查询匹配"，然后使用$slice 或 $arrayElemAt".

结果是:

<代码>{"_id" : ObjectId("593921425ccc8150f35e7663"),用户 2":3，身体":hiii 23"}{"_id" : ObjectId("593921425ccc8150f35e7664"),用户 2":4，身体":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"
}

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