合并嵌套在陣列mongoose中的物件內的$lookup值

所以我有 2 個模型user& form。

用戶架構

firstName: {
  type: String,
  required: true,
},
lastName: {
  type: String,
  required: true,
},
email: {
  type: String,
  required: true,
}

表單架構

 approvalLog: [
      {
        attachments: {
          type: [String],
        },
        by: {
          type: ObjectId,
        },
        comment: {
          type: String,
        },
        date: {
          type: Date,
        },
      },
    ],
 userId: {
      type: ObjectId,
      required: true,
    },
... other form parameters

回傳表單時，我試圖將每個用戶的用戶資訊聚合approvalLog到各自的物件中，如下所示。

{
...other form info
 approvalLog: [
    {
      attachments: [],
      _id: '619cc4953de8413b548f61a6',
      by: '619cba9cd64af530448b6347',
      comment: 'visit store for disburement',
      date: '2021-11-23T10:38:13.565Z',
      user: {
        _id: '619cba9cd64af530448b6347',
        firstName: 'admin',
        lastName: 'user',
        email: '[email protected]',
      },
    },
    {
      attachments: [],
      _id: '619cc4ec3ea3e940a42b2d01',
      by: '619cbd7b3de8413b548f61a0',
      comment: '',
      date: '2021-11-23T10:39:40.168Z',
      user: {
        _id: '619cbd7b3de8413b548f61a0',
        firstName: 'sam',
        lastName: 'ben',
        email: '[email protected]',
      },
    },
    {
      attachments: [],
      _id: '61a9deab8f472c52d8bac095',
      by: '61a87fd93dac9b209096ed94',
      comment: '',
      date: '2021-12-03T09:08:59.479Z',
      user: {
        _id: '61a87fd93dac9b209096ed94',
        firstName: 'john',
        lastName: 'doe',
        email: '[email protected]',
      },
    },
  ],
}

我目前的代碼是

 Form.aggregate([
      {
      $lookup: {
        from: 'users',
        localField: 'approvalLog.by',
        foreignField: '_id',
        as: 'approvedBy',
      },
    },
    { $addFields: { 'approvalLog.user': { $arrayElemAt: ['$approvedBy', 0] } } },
 ])

但它只為所有物件回傳相同的用戶。如何為每個索引附加匹配的用戶？

我也試過

Form.aggregate([
      {
      $lookup: {
        from: 'users',
        localField: 'approvalLog.by',
        foreignField: '_id',
        as: 'approvedBy',
      },
    },
    {
      $addFields: {
        approvalLog: {
          $map: {
            input: { $zip: { inputs: ['$approvalLog', '$approvedBy'] } },
            in: { $mergeObjects: '$$this' },
          },
        },
      },
    },
 ])
  

這會將正確的用戶添加到他們各自的物件中，但我只能將其添加到根物件而不是新物件。

uj5u.com熱心網友回復：

你可以試試這個方法，

• $map 迭代回圈 approvalLog
• $filter迭代approvedBy陣列回圈并搜索用戶IDby
• $arrayElemAt 從上面的過濾結果中獲取第一個元素
• $mergeObjects合并approvalLog和過濾用戶的當前物件屬性
• $$REMOVE不需要approvedBy現在

await Form.aggregate([
  {
    $lookup: {
      from: "users",
      localField: "approvalLog.by",
      foreignField: "_id",
      as: "approvedBy"
    }
  },
  {
    $addFields: {
      approvalLog: {
        $map: {
          input: "$approvalLog",
          as: "a",
          in: {
            $mergeObjects: [
              "$$a",
              {
                user: {
                  $arrayElemAt: [
                    {
                      $filter: {
                        input: "$approvedBy",
                        cond: { $eq: ["$$a.by", "$$this._id"] }
                      }
                    },
                    0
                  ]
                }
              }
            ]
          }
        }
      },
      approvedBy: "$$REMOVE"
    }
  }
])

操場

第二種方法使用$unwind，

• $unwind解構approvalLog陣列
• $lookup 有用戶收藏
• $addFields并$arrayElemAt從查找結果中獲取第一個元素
• $group通過_id并重建approvalLog陣列并獲取其他所需屬性的第一個值

await Form.aggregate([
  { $unwind: "$approvalLog" },
  {
    $lookup: {
      from: "users",
      localField: "approvalLog.by",
      foreignField: "_id",
      as: "approvalLog.user"
    }
  },
  {
    $addFields: {
      "approvalLog.user": {
        $arrayElemAt: ["$approvalLog.user", 0]
      }
    }
  },
  {
    $group: {
      _id: "$_id",
      approvalLog: { $push: "$approvalLog" },
      userId: { $first: "$userId" },
      // add your other properties like userId
    }
  }
])

操場

標籤：MongoDB 猫鼬 聚合框架

上一篇：在ASP.NETMVC中從不同模型呼叫視圖

下一篇：Vb.net如何顯示文本檔案中數字的頻率