帶有陣列和嵌套查找的MongoDB聚合

我正在努力將查詢放在一起，以完全按照我的意愿聚合資料。如果有人能幫忙解決這個問題，我將不勝感激！

合集是：

收藏 1

[
 {
   "_id": 1,
   "name": "Collection 1:1",
   "collection_2_ids": [5, 6]
 },
 {
   "_id": 2,
   "name": "Collection 1:2",
   "collection_2_ids": [8, 9]
 }
]

收藏 2

[
 {
   "_id": 5,
   "name": "collection 2:5",
   "collection_1_id": 1,
   "collection_3_id": 12
 },
 {
   "_id": 6,
   "name": "collection 2:6",
   "collection_1_id": 1,
   "collection_3_id": 13
 },
 {
   "_id": 8,
   "name": "collection 2:8",
   "collection_1_id": 2,
   "collection_3_id": 14
 },
 {
   "_id": 9,
   "name": "collection 2:9",
   "collection_1_id": 2,
   "collection_3_id": 15
 },
]

集合 3：

[
 {
   "_id": 12,
   "name": "collection 3:12"
 },
 {
   "_id": 13,
   "name": "collection 3:13"
 },
 {
   "_id": 14,
   "name": "collection 3:14"
 },
 {
   "_id": 15,
   "name": "collection 3:15"
 }
]

我想從中得到的是：

[
 {
   "_id": 1,
   "name": "Collection 1:1",
   "collection_2_documents": [
     {
       "_id": 5,
       "name": "collection 2:5",
       "collection_1_id": 1,
       "collection_3_id": 12,
       "collection_3_document": {
          "_id": 12,
          "name": "collection 3:12"
        }
     },
     {
       "_id": 6,
       "name": "collection 2:6",
       "collection_1_id": 1,
       "collection_3_id": 13,
       "collection_3_document": {
          "_id": 12,
          "name": "collection 3:12"
        }
     }
  ]
 },
 {
   "_id": 2,
   "name": "Collection 1:2",
   "collection_2_documents": [
       {
       "_id": 8,
       "name": "collection 2:8",
       "collection_1_id": 2,
       "collection_3_id": 14,
       "collection_3_document": {
          "_id": 14,
          "name": "collection 3:14"
        }
     },
     {
       "_id": 9,
       "name": "collection 2:9",
       "collection_1_id": 2,
       "collection_3_id": 15,
       "collection_3_document": {
          "_id": 15,
          "name": "collection 3:15"
        }
     }
   ]
 }
]

這是我當前的聚合/查找，它從集合 2 和 3 中回傳單獨的檔案陣列。

[  
  {
    $lookup: {
      from: 'Collection 2',
      localField: 'collection_2_ids',
      foreignField: '_id',
      as: 'collection_2_documents'
    }
  },
  {
    $lookup: {
      from: 'Collection 3',
      localField: 'collection_2_documents.collection_3_id',
      foreignField: '_id',
      as: 'collection_3_document'
    }
  }
]

uj5u.com熱心網友回復：

您必須使用stagepipeline選項$lookup并$lookup在 collection2 的查找中嵌套在 collection3 上使用

db.collection1.aggregate([
  {
    "$lookup": {
      "from": "collection2",
      "let": {
        "sourceCollection_2_ids": "$collection_2_ids"
      },
      "pipeline": [
        {
          "$match": {
            "$expr": {
              "$in": [
                "$_id",
                "$$sourceCollection_2_ids"
              ]
            }
          }
        },
        {
          "$lookup": {
            "from": "collection3",
            "let": {
              "sourceCollection_3_id": "$collection_3_id"
            },
            "pipeline": [
              {
                "$match": {
                  "$expr": {
                    "$eq": [
                      "$_id",
                      "$$sourceCollection_3_id"
                    ]
                  },
                  
                }
              },
              
            ],
            "as": "collection_3_document"
          }
        },
        {
          "$set": {
            "collection_3_document": {
              "$arrayElemAt": [
                "$collection_3_document",
                0
              ]
            }
          }
        },
        
      ],
      "as": "collection_2_documents"
    }
  },
  
])

Mongo Playground 示例執行

標籤：MongoDB 总计的 mongodb-图集

上一篇：MongoDB：從嵌入檔案中檢索值，沒有父檔案

下一篇：將mongodb指南針與node.js連接