使用聚合框架，如何使用管道中的資料修飾MongoDB中的集合（有點右外連接）

db.Parent.insertMany([
{ParentName: "MS", Type: "TAA"},
{ParentName: "MS", Type: "TAB"},
{ParentName: "MS", Type: "TAC"},
{ParentName: "MS", Type: "TAD"},
{ParentName: "GP", Type: "TAA"},
{ParentName: "GP", Type: "TAB"},
{ParentName: "GP", Type: "TAC"},
])

db.Mapper.insertMany([
{Type: "TAA", Identifier: "RA"},
{Type: "TAB", Identifier: "SA"},
{Type: "TAC", Identifier: "TA"},
{Type: "TAD", Identifier: "WA"},
])

db.Items.insertMany ([
{Name: "One", Identifier: "RA"},
{Name: "Two", Identifier: "RA"},
{Name: "Three", Identifier: "RA"},
{Name: "Four", Identifier: "SA"},
{Name: "Five", Identifier: "SA"},
{Name: "Six", Identifier: "WA"},
])

為“GP”的給定的輸入引數欲embelishItems與Type從Parent經由映射Mapper，使得輸出如下。我想要所有專案，無論是否有來自Parent/ 的匹配項Mapper。

[
{Name: "One",  Identifier: "RA", Type: "TAA"},
{Name: "Two",  Identifier: "RA", Type: "TAA"},
{Name: "Three",Identifier: "RA", Type: "TAA"},
{Name: "Four", Identifier: "SA", Type: "TAB"},
{Name: "Five", Identifier: "SA", Type: "TAB"},
{Name: "Six",  Identifier: "WA},
]

我已經為Mapper給定的“GP”輸入選擇了正確的行Parent并被困在那里。

我不確定是否可以切換查詢并開始Items然后插入任何匹配項。

db.Parent.aggregate(
{$match: {ParentName: "GP}},
{$project: {Type:1, _id:0}},
{$group: {_id:1, types: {$addToSet: "$Type"}}},
{$lookup: {
  from: "Mapper",
  let: {types: "$types"},
  pipeline:[{
    $match: {$expr: {$in: ["$Type", "$$types"]}}    
  }],
  as: "mapper"
}},
??? Right outer join with Items here ???
).pretty()

uj5u.com熱心網友回復：

查詢1

• 非嵌套連接（先展開）
• lookup與Mapper上Identifier
• unwind并且project只保留我們需要的東西
• lookup與Parent2個creterias
  • "$ParentName" = "GP"
  • and same Type
• unwind以preserveNullAndEmptyArrays保持那些沒有加入也
• project 只保留需要的東西

測驗代碼在這里

items.aggregate(
[{"$lookup": 
    {"from": "Mapper",
      "localField": "Identifier",
      "foreignField": "Identifier",
      "as": "joined"}},
  {"$unwind": {"path": "$joined"}},
  {"$project": {"Type": "$joined.Type", "Name": 1, "Identifier": 1}},
  {"$lookup": 
    {"from": "Parent",
      "let": {"type": "$Type", "indentifier": "$Identifier"},
      "pipeline": 
      [{"$match": 
          {"$expr": 
            {"$and": 
              [{"$eq": ["$ParentName", "GP"]}, {"$eq": ["$$type", "$Type"]}]}}},
        {"$set": {"Identifier": "$$indentifier"}}],
      "as": "joined"}},
  {"$unwind": {"path": "$joined", "preserveNullAndEmptyArrays": true}},
  {"$project": 
    {"_id": 0, "Name": 1, "Identifier": "$joined.Identifier", "Type": 1}}])

查詢2

• 嵌套連接
• 加入 itemIndetifier
• 嵌套連接型別僅當 ParentName="GP"
• 使用preserveNullAndEmptyArrays上述選項放松身心
• 最后放松
• 保留我們需要的專案

測驗代碼在這里

items.aggregate(
[{"$lookup": 
    {"from": "Mapper",
      "let": {"itemIndetifier": "$Identifier"},
      "pipeline": 
      [{"$match": {"$expr": {"$eq": ["$$itemIndetifier", "$Identifier"]}}},
        {"$lookup": 
          {"from": "Parent",
            "let": {"type": "$Type"},
            "pipeline": 
            [{"$match": 
                {"$expr": 
                  {"$and": 
                    [{"$eq": ["$ParentName", "GP"]},
                      {"$eq": ["$$type", "$Type"]}]}}}],
            "as": "joined1"}},
        {"$unwind": 
          {"path": "$joined1", "preserveNullAndEmptyArrays": true}}],
      "as": "joined2"}},
  {"$unwind": {"path": "$joined2"}},
  {"$project": 
    {"_id" : 0,"Name": 1, "Identifier": 1, "Type": "$joined2.joined1.Type"}}])

* 用于管道查找的索引需要 MongoDB 5，如果您在資料上對這 2 個進行基準測驗，請發送一些反饋，如果可以的話，哪個更快。我認為這兩個查詢都可以滿足您的需求。

標籤：MongoDB mongodb-查询 聚合框架

上一篇：MongoDB：按組聚合后按唯一值計數

下一篇：MongoDB-獲取不同的陣列