带最大子凭证的退货凭证 [英] Return Document with Max Sub Document
问题描述
我试图根据日期值返回包含最大子文档的文档.到目前为止，我已经能够创建正确的对象，但是查询将返回所有子文档，而不是具有最大日期的子文档.例如，我的数据存储为:
I am trying to return a document with it's maximum subdocument based upon a date value. So far I am able to create the correct object, however the query is returning all subdocuments instead of the ones with the maximum date. For example, my data is stored as:
{ value: 1,
_id: 5cb9ea0c75c61525e0176f96,
name: 'Test',
category: 'Development',
subcategory: 'Programming Languages',
status: 'Supported',
description: 'Test',
change:
[ { version: 1,
who: 'ATL User',
when: 2019-04-19T15:30:39.912Z,
what: 'Item Creation' },
{ version: 2,
who: 'ATL Other User',
when: 2019-04-19T15:30:39.912Z,
what: 'Name Change' } ],
}
在我的查询中，我选择所有具有相同subcategory以及具有name的项.然后，我在对象中投影所需的所有值，展开并对数组进行排序，然后返回查询结果.从结构上讲，这可以为我提供在此处建模的正确输出:
In my query I am selecting all items that have the same subcategory, as well with having their name exist. I then project all the values that I need within the object, unwind and sort the array, and return the query results. Structure wise, this gets me the correct output modeled here:
{
_id: 5cb9ea0c75c61525e0176f96,
name: 'Test',
category: 'Development',
subcategory: 'Programming Languages',
status: 'Supported',
description: 'Test',
change: {
"who": "ATL User",
"when": ISODate("2019-04-19T17:11:36Z")
}
}
这里的问题是，如果一个文档包含多个子文档-或版本-则查询也会返回这些子文档，而不是忽略它们，而只保留最大日期(如果项目Test具有三个版本，则三个Test文档退回).
The issue here is that if a document has multiple sub-documents - or versions - then the query returns those as well instead of omitting them, leaving only the maximum date (if item Test had three versions, then three Test documents get returned).
要在此查询中否定其他文档，我应该怎么看?
What should I be looking at in order to negate those other documents with this query?
db.items.aggregate([
{$match: {subcategory: "Programming Languages", name: {$exists: true}}},
{$project: {"name": 1,
"category": 1,
"subcategory": 1,
"status": 1,
"description": 1,
"change.who": 1,
"change.when": {$max: "$change.when"}}},
{$unwind: "$change"},
{$sort: {"change.when": -1}}
]);
推荐答案
首先，让我们以人们可以使用它并产生所需结果的方式来显示您的数据:
First off, let's show your data in a way people can use it and produce a desired result:
{ value: 1,
_id: ObjectId('5cb9ea0c75c61525e0176f96'),
name: 'Test',
category: 'Development',
subcategory: 'Programming Languages',
status: 'Supported',
description: 'Test',
change:
[ { version: 1,
who: 'ATL User',
when: new Date('2019-04-19T15:30:39.912Z'),
what: 'Item Creation' },
{ version: 2,
who: 'ATL Other User',
when: new Date('2019-04-19T15:31:39.912Z'),
what: 'Name Change' } ],
}
Note that the "when" dates are in fact different so there will be a $max value and they are not just the same. Now we can run through the cases
这里的基本情况是使用 $arrayElemAt 和 $indexOfArray 运算符以返回匹配的 $max 值:
The basic case here is to use the $arrayElemAt and $indexOfArray operators to return the matching $max value:
db.items.aggregate([
{ "$match": {
"subcategory": "Programming Languages", "name": { "$exists": true }
}},
{ "$addFields": {
"change": {
"$arrayElemAt": [
"$change",
{ "$indexOfArray": [
"$change.when",
{ "$max": "$change.when" }
]}
]
}
}}
])
返回:
{
"_id" : ObjectId("5cb9ea0c75c61525e0176f96"),
"value" : 1,
"name" : "Test",
"category" : "Development",
"subcategory" : "Programming Languages",
"status" : "Supported",
"description" : "Test",
"change" : {
"version" : 2,
"who" : "ATL Other User",
"when" : ISODate("2019-04-19T15:31:39.912Z"),
"what" : "Name Change"
}
}
基本上，"$max": "$change.when"返回的值是该值数组中的最大值".然后，您可以通过 $indexOfArray找到该值数组的匹配索引" ，它返回找到的第一个匹配索引.然后，该索引"位置(实际上只是一个以相同顺序转置的"when"值数组)与数组中指定索引位置提取整个对象".
Basically the "$max": "$change.when" returns the value which is the "maximum" from within that array of values. You then find the matching "index" of that array of values via $indexOfArray which returns the first matching index found. That "index" position ( from actually just an array of "when" values transposed in the same order ) is then used with $arrayElemAt to extract the "whole object" from the "change" array at the specified index position.
与 $max 差不多，除了这次，我们 $filter 返回多个可能" 值与 $max 值:
Pretty much the same thing with $max, except this time we $filter to return the multiple "possible" values matching that $max value:
db.items.aggregate([
{ "$match": {
"subcategory": "Programming Languages", "name": { "$exists": true }
}},
{ "$addFields": {
"change": {
"$filter": {
"input": "$change",
"cond": {
"$eq": [ "$$this.when", { "$max": "$change.when" } ]
}
}
}
}}
])
返回:
{
"_id" : ObjectId("5cb9ea0c75c61525e0176f96"),
"value" : 1,
"name" : "Test",
"category" : "Development",
"subcategory" : "Programming Languages",
"status" : "Supported",
"description" : "Test",
"change" : [
{
"version" : 2,
"who" : "ATL Other User",
"when" : ISODate("2019-04-19T15:31:39.912Z"),
"what" : "Name Change"
}
]
}
所以 $max 当然是相同的但这次，该操作符返回的奇异值用于 $eq 在 $filter 中进行比较.这将检查每个数组元素，并查看 current "when"值("$$this.when").其中等于" 则返回该元素.
So the $max is of course the same but this time the singular value returned by that operator is used in an $eq comparison within $filter. This inspects each array element and looks at the current "when" value ( "$$this.when" ). Where "equal" then the element is returned.
与第一种方法基本相同，但$filter允许返回多个" 元素.因此，所有与相同相同
Basically the same as the first approach but with the exception that $filter allows "multiple" elements to be returned. Therefore everything with the same $max value.
现在，您可能会注意到，在我包括的示例数据中(根据您自己的数据改编，但带有实际的最大"日期)，最大"值实际上是数组中的 last 值.由于 $push (默认情况下)追加" 到现有数组内容的末尾.因此，较新" 条目将倾向于位于数组的 end .
Now you might note that in the example data I included ( adapted from your own but with an actual "max" date ) the "max" value is in fact the last value in the array. This may just naturally happen as a result that $push ( by default ) "appends" to the end of the existing array content. So "newer" entries will tend to be at the end of the array.
这当然是默认行为，但是有充分的理由使您可能" 想要更改它.简而言之，获取最新" 数组条目的最好方法实际上是从数组中返回第一个元素.
This of course is the default behavior, but there are good reasons why you "may" want to change that. In short the best way to get the "most recent" array entry is to in fact return the first element from the array.
您实际上要做的就是确保最新" 实际上是第一而不是最后添加的.有两种方法:
All you actually need to do is ensure the "most recent" is actually added first rather than last. There are two approaches:
-
使用
$position预添加"数组项::这是
Use
$positionto "pre-pend" array items: This is a simple modifier to$pushusing the0position in order to always add to the front:
db.items.updateOne(
{ "_id" : ObjectId("5cb9ea0c75c61525e0176f96") },
{ "$push": {
"change": {
"$each": [{
"version": 3,
"who": "ATL User",
"when": new Date(),
"what": "Another change"
}],
"$position": 0
}
}}
)
这会将文档更改为:
{
"_id" : ObjectId("5cb9ea0c75c61525e0176f96"),
"value" : 1,
"name" : "Test",
"category" : "Development",
"subcategory" : "Programming Languages",
"status" : "Supported",
"description" : "Test",
"change" : [
{
"version" : 3,
"who" : "ATL User",
"when" : ISODate("2019-04-20T02:40:30.024Z"),
"what" : "Another change"
},
{
"version" : 1,
"who" : "ATL User",
"when" : ISODate("2019-04-19T15:30:39.912Z"),
"what" : "Item Creation"
},
{
"version" : 2,
"who" : "ATL Other User",
"when" : ISODate("2019-04-19T15:31:39.912Z"),
"what" : "Name Change"
}
]
}
请注意，这将需要您实际先对所有数组元素进行反向"操作，以便最新"已经在最前面，因此可以保持顺序.值得庆幸的是，第二种方法已经涵盖了这一点.
Note that this would require you to actually go and "reverse" all your array elements beforehand so that the "newest" was at the front already so the order was maintained. Thankfully this is somewhat covered in the second approach...
-
使用
$sort修改每个$push上的文档:这是另一个修饰符，它实际上在每次添加新项目时都会对原子进行重新排序".正常用法与$each如上，甚至只是一个空"数组，以应用$sort仅适用于现有数据:
Use
$sortto modify the documents in order on each$push: And this is the other modifier which actually "re-sorts" atomically on every new item addition. Normal usage is basically the same with any new items to$eachas above, or even just an "empty" array in order to apply the$sortto existing data only:
db.items.updateOne(
{ "_id" : ObjectId("5cb9ea0c75c61525e0176f96") },
{ "$push": {
"change": {
"$each": [],
"$sort": { "when": -1 }
}
}}
)
结果:
{
"_id" : ObjectId("5cb9ea0c75c61525e0176f96"),
"value" : 1,
"name" : "Test",
"category" : "Development",
"subcategory" : "Programming Languages",
"status" : "Supported",
"description" : "Test",
"change" : [
{
"version" : 3,
"who" : "ATL User",
"when" : ISODate("2019-04-20T02:40:30.024Z"),
"what" : "Another change"
},
{
"version" : 2,
"who" : "ATL Other User",
"when" : ISODate("2019-04-19T15:31:39.912Z"),
"what" : "Name Change"
},
{
"version" : 1,
"who" : "ATL User",
"when" : ISODate("2019-04-19T15:30:39.912Z"),
"what" : "Item Creation"
}
]
}
可能需要花费一分钟的时间来理解为什么 $push ，以便 $sort 这样的数组，但是通常的目的是对数组进行修改以改变"诸如Date值之类的属性，而您将使用这样的语句来反映这些更改.或者确实只是使用 $sort 添加新项目，然后可以解决.
It might take a minute to absorb why you would $push in order to $sort an array like this, but the general intent is when modifications might be made to an array which "alter" a property like a Date value being sorted on and you would use such a statement to reflect those changes. Or indeed just add new items with the $sort and let it work out.
那么为什么存储" 这样排列的数组呢?如前所述，您希望 first 项作为最近" ，然后返回该查询的查询变成:
So why "store" the array ordered like this? As mentioned earlier you want the first item as the "most recent", and then the query to return that simply becomes:
db.items.find(
{
"subcategory": "Programming Languages",
"name": { "$exists": true }
},
{ "change": { "$slice": 1 } }
)
返回:
{
"_id" : ObjectId("5cb9ea0c75c61525e0176f96"),
"value" : 1,
"name" : "Test",
"category" : "Development",
"subcategory" : "Programming Languages",
"status" : "Supported",
"description" : "Test",
"change" : [
{
"version" : 3,
"who" : "ATL User",
"when" : ISODate("2019-04-20T02:40:30.024Z"),
"what" : "Another change"
}
]
}
因此，可以仅使用 $slice 通过已知索引提取数组项.从技术上讲，您可以只在此处使用-1来返回数组的 last 项，但是以最新的优先顺序进行重新排序允许进行其他操作，例如确认最后的修改是由a进行的.某些用户和/或其他条件(例如日期范围约束).即:
So the $slice can then be used just to extract array items by known indexes. Technically you can just use -1 there in order to return the last item of the array anyway, but the reordering where the most recent is first allows for other things like confirming the last modification was made by a certain user, and/or other conditions like a date range constraint. i.e:
db.items.find(
{
"subcategory": "Programming Languages",
"name": { "$exists": true },
"change.0.who": "ATL User",
"change.0.when": { "$gt": new Date("2018-04-01") }
},
{ "change": { "$slice": 1 } }
)
请注意，类似"change.-1.when"的内容是非法声明，这基本上是我们对数组重新排序的原因，因此您可以将 legal 0用作 first 的-1表示 last .
Noting here that something like "change.-1.when" is an illegal statement, which is basically why we reorder the array so you can use the legal 0 for first instead of -1 for last.
因此，您可以执行几种不同的操作，通过使用聚合方法来过滤数组内容，或者在对数据的实际存储方式进行了一些修改之后，通过标准查询表单.使用哪种查询取决于您自己的情况，但应注意，任何标准查询表单的运行速度明显快于通过聚合框架或任何计算出的运算符进行的任何处理.
So there are several different things you can do, either by using the aggregation approach to filtering the array content or via standard query forms after making some modification to how the data is actually stored. Which one to use depends on your own circumstances, but it should be noted that any of the standard query forms will run notably faster than any manipulation via the aggregation framework or any computed operators.
这篇关于带最大子凭证的退货凭证的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
