聚合 $lookup 不返回元素原始数组顺序 [英] Aggregate $lookup does not return elements original array order
问题描述
查询返回元素在其集合中的放置顺序，忽略初始数组的顺序.这会影响我们系统的功能.是否有任何额外的命令可以将其按正确的顺序排列?有什么解决方法吗?
下面是一个简单的例子:
Collection1 文档
<代码>{"_id":ObjectId("5c781752176c512f180048e3"),"姓名":"佩德罗",类":[{"ID": ObjectId("5c7af2b2f6f6e47c9060d7ce") },{"ID": ObjectId("5c7af2bcf6f6e47c9060d7cf") },{ID":ObjectId(5c7af2aaf6f6e47c9060d7cd")}]}Collection2 文档
<代码>{"_id":ObjectId("5c7af2aaf6f6e47c9060d7cd"),变量1":A"},{"_id": ObjectId("5c7af2b2f6f6e47c9060d7ce"),变量1":B"},{"_id": ObjectId("5c7af2bcf6f6e47c9060d7cf"),变量1":C"}查询:
聚合(管道 = '[{"$match": {"_id": {"$oid": "5c781752176c512f180048e3"}}},{"$lookup": {"from": "collection2", "localField": "Classes.ID", "foreignField": "_id", "as": "Collection2_doc"}}]')返回:
结果顺序:
<预><代码>[{"_id":ObjectId("5c7af2aaf6f6e47c9060d7cd"),变量1":A"},{"_id": ObjectId("5c7af2b2f6f6e47c9060d7ce"),变量1":B"},{"_id": ObjectId("5c7af2bcf6f6e47c9060d7cf"),变量1":C"}]预期顺序(第一个文档数组顺序):
<预><代码>[{"_id": ObjectId("5c7af2b2f6f6e47c9060d7ce"),变量1":B"},{"_id": ObjectId("5c7af2bcf6f6e47c9060d7cf"),变量1":C"},{"_id":ObjectId("5c7af2aaf6f6e47c9060d7cd"),变量1":A"}]是否有任何额外的命令 ex.$sort 可用于按照原始数组顺序返回它?
这是设计的"$lookup 实现.实际上发生的幕后"是MongoDB内部转换$lookup 到新的 expressive 格式，使用 $expr 和 $in.即使在实现这种表达性形式之前的版本中，值数组"的内部机制也非常相似.
这里的解决方案是保留原始数组的副本作为重新排序joined"项的参考:
collection.aggregate([{"$match": {"_id": ObjectId("5c781752176c512f180048e3") }},{$查找":{来自":collection2"，让":{classIds":$Classes.ID"},管道":[{$匹配":{"$expr": { "$in": [ "$_id", "$$classIds";] }}},{$addFields":{排序":{"$indexOfArray": [ "$$classIds", "$_id";]}}},{ "$sort": { "sort": 1 } },{ "$addFields": { "sort": "$$REMOVE";}}],作为":结果"}}])或者通过传统的 $lookup 用法:
collection.aggregate([{$match":{_id":ObjectId(5c781752176c512f180048e3")}}，{$查找":{来自":collection2"，localField":Classes.ID"，foreignField":_id"，作为":结果"}},{ "$unwind": "$results";},{$addFields":{排序":{$indexOfArray": [ $Classes.ID", $results._id";]}}},{ "$sort": { "_id": 1, "sort": 1 } },{$组":{_id":$_id"，姓名":{$first":$姓名"},类":{$first":$类"},结果":{$push":$results"；}}}])两种变体产生相同的输出:
<代码>{_id": ObjectId(5c781752176c512f180048e3"),姓名":佩德罗"，类": [{身份证": ObjectId(5c7af2b2f6f6e47c9060d7ce")},{身份证": ObjectId(5c7af2bcf6f6e47c9060d7cf")},{身份证": ObjectId(5c7af2aaf6f6e47c9060d7cd")}],结果": [{_id": ObjectId(5c7af2b2f6f6e47c9060d7ce"),变量1":B"},{_id": ObjectId(5c7af2bcf6f6e47c9060d7cf"),变量1":C"},{_id": ObjectId(5c7af2aaf6f6e47c9060d7cd"),变量1":A"}]}一般概念是使用 $indexOfArray 与 joined" 内容中的 _id 值进行比较，以找到它在原始源中的 index" 位置"$Classes.ID" 中的数组.不同的 $lookup 语法变体有您如何访问此副本以及您如何基本重建的不同方法.
$sortcourse 设置实际文档的顺序，要么在管道内处理用于表达形式，要么通过 $unwind.在您使用 $unwind 的地方，您会然后 $group 回到原来的文档形式.
注意:此处的用法示例依赖于 MongoDB 3.4 的 $indexOfArray 至少和 $$REMOVE 与 MongoDB 3.6 一致，expressive $lookup 也是如此.
还有其他方法可以为以前的版本重新排序数组，但这些方法在 MongoDB 的 $in 子句中有更详细的说明保证订单.实际上，您目前应该作为生产 MongoDB 版本运行的最低限度是 3.4 版本.
请参阅 MongoDB 服务器 下的 支持政策strong> 有关受支持版本和结束日期的完整详细信息.
The query returns the order in which elements are placed in their collection, ignoring the order of the initial array. This affects the function of our system. Is there any extra command to put it in the correct order? Is there any workaround available?
Here follows a simple example:
Collection1 Document
{
"_id":ObjectId("5c781752176c512f180048e3"),
"Name":"Pedro",
"Classes":[
{"ID": ObjectId("5c7af2b2f6f6e47c9060d7ce") },
{"ID": ObjectId("5c7af2bcf6f6e47c9060d7cf") },
{"ID": ObjectId("5c7af2aaf6f6e47c9060d7cd") }
]
}
Collection2 Documents
{
"_id":ObjectId("5c7af2aaf6f6e47c9060d7cd"),
"variable1":"A"
},
{
"_id": ObjectId("5c7af2b2f6f6e47c9060d7ce"),
"variable1":"B"
},
{
"_id": ObjectId("5c7af2bcf6f6e47c9060d7cf"),
"variable1":"C"
}
The query:
aggregate(
pipeline = '[
{"$match": {"_id": {"$oid": "5c781752176c512f180048e3"}}},
{"$lookup": {"from": "collection2", "localField": "Classes.ID", "foreignField": "_id", "as": "Collection2_doc"}}
]'
)
Returns:
Result's order:
[
{
"_id":ObjectId("5c7af2aaf6f6e47c9060d7cd"),
"variable1":"A"
},
{
"_id": ObjectId("5c7af2b2f6f6e47c9060d7ce"),
"variable1":"B"
},
{
"_id": ObjectId("5c7af2bcf6f6e47c9060d7cf"),
"variable1":"C"
}
]
Expected order (first document array order):
[
{
"_id": ObjectId("5c7af2b2f6f6e47c9060d7ce"),
"variable1":"B"
},
{
"_id": ObjectId("5c7af2bcf6f6e47c9060d7cf"),
"variable1":"C"
},
{
"_id":ObjectId("5c7af2aaf6f6e47c9060d7cd"),
"variable1":"A"
}
]
Are there any extra command ex. $sort that could be used to return it respecting the original arrays order?
This is "by design" of the $lookup implementation. What actually happens "under the hood" is MongoDB internall converts the arguments in the $lookup to the new expressive format using $expr and $in. Even in versions prior to when this expressive form was implemented, the internal mechanics for an "array of values" was really much the same.
The solution here is to maintain a copy of the original array as a reference for reordering the "joined" items:
collection.aggregate([
{"$match": {"_id": ObjectId("5c781752176c512f180048e3") }},
{"$lookup": {
"from": "collection2",
"let": { "classIds": "$Classes.ID" },
"pipeline": [
{ "$match": {
"$expr": { "$in": [ "$_id", "$$classIds" ] }
}},
{ "$addFields": {
"sort": {
"$indexOfArray": [ "$$classIds", "$_id" ]
}
}},
{ "$sort": { "sort": 1 } },
{ "$addFields": { "sort": "$$REMOVE" }}
],
"as": "results"
}}
])
Or by the legacy $lookup usage:
collection.aggregate([
{"$match": {"_id": ObjectId("5c781752176c512f180048e3") }},
{"$lookup": {
"from": "collection2",
"localField": "Classes.ID",
"foreignField": "_id",
"as": "results"
}},
{ "$unwind": "$results" },
{ "$addFields": {
"sort": {
"$indexOfArray": [ "$Classes.ID", "$results._id" ]
}
}},
{ "$sort": { "_id": 1, "sort": 1 } },
{ "$group": {
"_id": "$_id",
"Name": { "$first": "$Name" },
"Classes": { "$first": "$Classes" },
"results": { "$push": "$results" }
}}
])
Both variants produce the same output:
{
"_id" : ObjectId("5c781752176c512f180048e3"),
"Name" : "Pedro",
"Classes" : [
{
"ID" : ObjectId("5c7af2b2f6f6e47c9060d7ce")
},
{
"ID" : ObjectId("5c7af2bcf6f6e47c9060d7cf")
},
{
"ID" : ObjectId("5c7af2aaf6f6e47c9060d7cd")
}
],
"results" : [
{
"_id" : ObjectId("5c7af2b2f6f6e47c9060d7ce"),
"variable1" : "B"
},
{
"_id" : ObjectId("5c7af2bcf6f6e47c9060d7cf"),
"variable1" : "C"
},
{
"_id" : ObjectId("5c7af2aaf6f6e47c9060d7cd"),
"variable1" : "A"
}
]
}
The general concept being to use $indexOfArray in comparison with the _id value from the "joined" content to find it's "index" position in the original source array from "$Classes.ID". The different $lookup syntax variants have different approaches to how you access this copy and how you basically reconstruct.
The $sort of course sets the order of actual documents, either being inside the pipeline processing for the expressive form, or via the exposed documents of $unwind. Where you used $unwind you would then $group back to the original document form.
NOTE: The usage examples here depend on MongoDB 3.4 for the
$indexOfArrayat least and the$$REMOVEaligns with MongoDB 3.6 as would the expressive$lookup.There are other approaches to re-ordering the array for prior releases, but these are demonstrated in more detail on Does MongoDB's $in clause guarantee order. Realistically the bare minimum you should presently be running as a production MongoDB version is the 3.4 release.
See Support Policy under MongoDB Server for the full details of supported releases and end dates.
这篇关于聚合 $lookup 不返回元素原始数组顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
