MongoDB聚合管道專案來自同一id的多個值-有解無憂

我在 mongoDB 管道上苦苦掙扎。我正在研究處理資料的 MERN 堆疊。

我們用表格向人們提問，表格被描述為一個會話，例如每個人一個會話。每個會話都記錄在這樣的表中：

{ _id: 1, created_at:"01/01/2021"}
{ _id: 2, created_at:"02/01/2021"}
{ _id: 3, created_at:"03/01/2021"}

他們所有的答案都存盤在一個表中，外鍵為 sessionId：

{ _id: 1, value:"Name1", sessionId : 1, typeofField :"name"}
{ _id: 2, value:"Firstname1", sessionId : 1, typeofField :"firstname"}
{ _id: 3, value:"Date of birth1", sessionId : 1, typeofField :"birthdate"}
{ _id: 4, value:"Name2", sessionId : 2, typeofField :"name"}
{ _id: 5, value:"Firstname2", sessionId : 2, typeofField :"firstname"}
{ _id: 6, value:"Date of birth2", sessionId : 2, typeofField :"birthdate"}

我如何將這些資料投影到會話的所有資訊中，如下所示：

{id :1, created_at:"01/01/2021", name : "Name1", firstname: "Firstname1", birthdate : "Date of Birth1"}
{id :2, created_at:"02/01/2021", name : "Name2", firstname: "Firstname2", birthdate : "Date of Birth2"}

uj5u.com熱心網友回復：

另一種解決方案，朝另一個方向發展（從答案到會話）：

c = db.answers.aggregate([
    // Bring all answers together as a k-v array:                                                                     
    {$group: {_id: "$sessionId", flds: {$push: {k: "$typeofField", v: "$value"}}}}

    // Do a 1:1 lookup:                                                                                               
    ,{$lookup: {from: "session", localField: "_id", foreignField: "_id", as: "Z"}}

    // We now have flds as a k-v array.  We know that Z[0] cotains both                                               
    // created_at and _id.  We seek to create a full k-v array that we can                                            
    // turn into the target object, so working the expression below "backwards"                                       
    // 1. Pull element 0 from the Z array                                                                             
    // 2. Turn that into a k-v array, e.g. [{k:_id,v:1},{k:created_at,v:02/01/2021}]                                  
    //    with $objectToArray.  Important: we pick up _id here.                                                       
    // 3. Concat the flds k-v array with the Xsession lookup k-v array                                                
    // 4. We now have a complete k-v representation of our data.  Use $arrayToObject                                  
    //    to turn (e.g.) {k:created_at,v:02/01/2021} into created_at:02/01/2021                                       
    // 5. Don't assign the object to a fld (like X).  Instead make that object the                                    
    //    new root.  newRoot is the only arg to $replaceRoot:                                                         
    ,{$replaceRoot: { newRoot:
        {$arrayToObject:
           {$concatArrays: [ "$flds", {$objectToArray: {$arrayElemAt: ["$Z",0]}} ] }}}}

]);

或者，如果您想要更多地控制欄位而不是獲取sesssion檔案中的所有內容：

c = db.answers.aggregate([
    {$group: {_id: "$sessionId", flds: {$push: {k: "$typeofField", v: "$value"}}}}
    ,{$lookup: {from: "session", localField: "_id", foreignField: "_id", as: "Z"}}

    // Don't want all the fields from the lookup?  No problem: wrap the                                               
    // $objectToArray with a filter and only let k = [_id,created_at,foo]                                             
    // or whatever else you want.  Make sure to always include _id.                                                   
    // Of course, if you want to exclude fields and keep the rest, just use                                           
    // the $not operator.  Be sure not to exclude _id; see commented cond below:                                      
    ,{$replaceRoot: {newRoot: {$arrayToObject: {$concatArrays: [ "$flds",
                     {$filter: {input: {$objectToArray: {$arrayElemAt: ["$Z",0]}},
                                   as: "z",
                                cond: {$in: ["$$z.k", ["_id","created_at","foo"]]}
                                //cond: {$not: {$in: ["$$z.k", ["foo"]]}}                                             
                               }}
                                                               ]
                                               }}
                    }}
]);

根據在中查找的材料數量session，您可能希望使用更高級的版本$lookup來過濾那里的欄位：

c = db.answers.aggregate([
    {$group: {_id: "$sessionId", flds: {$push: {k: "$typeofField", v: "$value"}}}}
    ,{$lookup: {from: "session",
                let: { sid: "$_id" },
                pipeline: [
                    {$match: {$expr: {$eq: [ "$_id", "$$sid" ]} }},
                    {$project: {"_id":true, "created_at":true,"foo":true}}
                ],
                as: "Z"
               }}
    ,{$replaceRoot: { newRoot:
        {$arrayToObject:
           {$concatArrays: [ "$flds", {$objectToArray: {$arrayElemAt: ["$Z",0]}} ] }}}}
]);

uj5u.com熱心網友回復：

這是我的解決方案：

查找$lookup階段會話的所有答案
將所有答案轉換為一個物件，例如： { [typeofField]: value }
將所有答案合并到一個物件
最后將新轉換的answers物件與根檔案（$$ROOT會話集合的檔案）合并

如果您不了解管道，我創建了一個 mongodb 游樂場（Playground Link），所以去嘗試一次執行一個階段。

請自行參考此管道中使用的階段和運算子的檔案。$lookup，$addFields，$arrayToObject，$mergeObjects，$replaceRoot，$unset。

注意：確保用于階段中的as欄位的值$lookup不會出現在的答案集合中typeofField，否則它將被$unset階段洗掉。因此，對于答案集合下方的管道，不應包含{ ... typeofField: "allAnswers" ... }.

管道

[
  {
    $lookup: {
      from: "answers",
      localField: "_id",
      foreignField: "sessionId",
      pipeline: [
        { $addFields: { keyValue: [["$typeofField", "$value"]] } },
        { $replaceRoot: { newRoot: { $arrayToObject: "$keyValue" } } },
      ],
      as: "allAnswers",
    },
  },
  {
    $replaceRoot: {
      newRoot: { $mergeObjects: [{ $mergeObjects: "$allAnswers" }, "$$ROOT"] },
    },
  },
  { $unset: "allAnswers" },
]

對于 5.0 之前的用戶，請使用以下查找：

    $lookup: {                                                                                                        
      from: "answers",                                                                                               
        let: { sid: "$_id" },                                                                                         
        pipeline: [                                                                                                   
            { $match: {$expr: {$eq: ["$sessionId", "$$sid"]}} },                                                      
            { $addFields: { keyValue: [["$typeofField", "$value"]] } }                                                
            ,{ $replaceRoot: { newRoot: { $arrayToObject: "$keyValue" } } },                                          
        ],                                                                                                            
        as: "allAnswers",                                                                                             
    },

轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/406698.html

標籤：

上一篇：如何增加一個物件中的值，該物件位于另一個物件中的陣列中

下一篇：MongoDB按時間間隔分組結果