聚合$ lookup不返回元素原始数组顺序 [英] Aggregate $lookup does not return elements original array order

问题描述

查询返回元素在其集合中的放置顺序，而忽略初始数组的顺序.这会影响我们系统的功能.是否有其他命令可以将其正确排列?有任何解决方法吗?

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文档

{
  "_id":ObjectId("5c781752176c512f180048e3"),
  "Name":"Pedro",
  "Classes":[
    {"ID": ObjectId("5c7af2b2f6f6e47c9060d7ce") },
    {"ID": ObjectId("5c7af2bcf6f6e47c9060d7cf") },
    {"ID": ObjectId("5c7af2aaf6f6e47c9060d7cd") }
  ]
}

Collection2文档

{
  "_id":ObjectId("5c7af2aaf6f6e47c9060d7cd"),
  "variable1":"A"
},

{
  "_id": ObjectId("5c7af2b2f6f6e47c9060d7ce"),
  "variable1":"B"
},

{
  "_id": ObjectId("5c7af2bcf6f6e47c9060d7cf"),
  "variable1":"C"
}

查询:

aggregate(
  pipeline = '[
  {"$match": {"_id": {"$oid": "5c781752176c512f180048e3"}}},
  {"$lookup": {"from": "collection2", "localField": "Classes.ID", "foreignField": "_id", "as": "Collection2_doc"}}
  ]'
)

返回:

结果的顺序:

[
    {
      "_id":ObjectId("5c7af2aaf6f6e47c9060d7cd"),
      "variable1":"A"
    },
    {
      "_id": ObjectId("5c7af2b2f6f6e47c9060d7ce"),
      "variable1":"B"
    },
    {
      "_id": ObjectId("5c7af2bcf6f6e47c9060d7cf"),
      "variable1":"C"
    }
]

预期顺序(第一个文档排列顺序):

[
    {
      "_id": ObjectId("5c7af2b2f6f6e47c9060d7ce"),
      "variable1":"B"
    },
    {
      "_id": ObjectId("5c7af2bcf6f6e47c9060d7cf"),
      "variable1":"C"
    },
    {
      "_id":ObjectId("5c7af2aaf6f6e47c9060d7cd"),
      "variable1":"A"
    }
]

例如，是否还有其他命令. $sort可以用来按照原始数组顺序返回它吗?

Are there any extra command ex. $sort that could be used to return it respecting the original arrays order?

推荐答案

这是中发生的事情是MongoDB internall 转换 $lookup 到新的 expressive 格式. mongodb.com/manual/reference/operator/query/expr/"rel =" noreferrer> $expr 和的内部机制实际上也是相同的.

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.

这里的解决方案是维护原始数组的副本，以作为对"joined" 项目进行重新排序的参考:

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

或者通过传统的 $lookup 用法:

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

一般概念是在比较中使用 $indexOfArray 使用"joined" 内容中的_id值在"$Classes.ID"的原始源数组中找到它的"index" 位置.不同的 $lookup 语法变体在访问方式上有不同的方法此副本以及您的基本构造方式.

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.

$sort 当然可以设置实际的顺序文档，可以在表达处理形式的管道处理中，也可以通过 $unwind 的地方，您将

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.

注意: $indexOfArray 和

NOTE: The usage examples here depend on MongoDB 3.4 for the $indexOfArray at least and the $$REMOVE aligns with MongoDB 3.6 as would the expressive $lookup.

还有其他方法可以对先前版本的阵列进行重新排序，但是这些方法在 Dogo MongoDB的$ in子句中有更详细的说明.保证订单.实际上，目前作为生产MongoDB版本运行的最低要求是3.4版本.

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.

请参见 MongoDB Server 下的 支持政策 . strong>，以获取受支持版本和结束日期的完整详细信息.

See Support Policy under MongoDB Server for the full details of supported releases and end dates.

这篇关于聚合$ lookup不返回元素原始数组顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！