Merge $lookup value inside objects nested in array mongoose-CodePudding

So I have 2 models user & form.

User Schema

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

Form Schema

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

When returning a form, I'm trying to aggregate the user info of every user in the approvalLog into their respective objects as below.

{
...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]',
      },
    },
  ],
}

My current code is

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

but it only returns the same user for all objects. How do I attach the matching user for each index?

I've also tried

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

This adds the right user to their respective objects, but I can only add the to the root object and not a new one.

CodePudding user response：

You can try the approach,

$map to iterate loop of approvalLog
$filter to iterate loop of approvedBy array and search for user id by
$arrayElemAt to get first element from above filtered result
$mergeObjects to merge current object properties of approvalLog and filtered user
$$REMOVE don't need approvedBy now

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

Playground

The second approach using $unwind,

$unwind deconstruct the approvalLog array
$lookup with user collection
$addFields and $arrayElemAt to get first element from lookup result
$group by _id and reconstruct the approvalLog array and get first value of other required properties

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

Playground