MongoDB Add/Replace Element from Separate Collections-CodePudding

I have two mongodb collections, processes and users and below I have example docs from within them. I am trying to query a process document but replace the userID's that are nested in the event objects with the names of the users instead of their ID's. What is the best way to do that given I may have several nested objects within the 'history' object? (Running on node and using the native driver, not using mongoose).

Process Document

{
    _id: ObjectId('63d96b68e7b92dceb334f4cb'),
    status: 'active',
    history: {
        {
            type: 'created',
            userID: '61e77cdedde2dbe1cbf8a250',
            date: 'Tue Jan 31 2023 17:31:32 GMT 0000 (Coordinated Universal Time)'
        },
        {
            type: 'updated',
            userID: 'd6xMtHTIX3QO0FifUPgoJLOLz872',
            date: 'Tue Jan 31 2023 18:31:32 GMT 0000 (Coordinated Universal Time)'
        },
        {
            type: 'updated',
            userID: '61e77cdedde2dbe1cbf8a250',
            date: 'Tue Jan 31 2023 19:31:32 GMT 0000 (Coordinated Universal Time)'
        },

    }
}

User 1 Document

{
    _id: ObjectId('d6xMtHTIX3QO0FifUPgoJLOLz872'),
    email: '[email protected]',
    firstname: 'Bobby',
    lastname: 'Tables',
}

User 2 Document

{
    _id: ObjectId('61e77cdedde2dbe1cbf8a250'),
    email: '[email protected]',
    firstname: 'Jenny',
    lastname: 'Tables',
}

Actual outpt I'm looking for

{
    _id: ObjectId('63d96b68e7b92dceb334f4cb'),
    status: 'active',
    history: {
        {
            type: 'created',
            userName: 'Jenny Tables',
            date: 'Tue Jan 31 2023 17:31:32 GMT 0000 (Coordinated Universal Time)'
        },
        {
            type: 'updated',
            userName: 'Bobby Tables',
            date: 'Tue Jan 31 2023 18:31:32 GMT 0000 (Coordinated Universal Time)'
        },
        {
            type: 'updated',
            userName: 'Jenny Tables',
            date: 'Tue Jan 31 2023 19:31:32 GMT 0000 (Coordinated Universal Time)'
        },

    }
}

Here is what I have tried, which I can get to yield existing data and update one record's name, but not all of them:

try {
    const db = mongo.getDB();
    const data = db.collection("processes");
    data.aggregate([
        {$sort: {_id: -1}}, 
        {$lookup: {from: 'users', localField: 'history.userID', foreignField: '_id', as: 'User'}},
        {$unwind: '$User'},
        {$addFields: {"history.userName": { '$concat': ['$User.firstname', ' ', '$User.lastname']}}},
        {$project:{_id: 1, status: 1, history: 1}}
    ]).toArray(function(err, result) {
        if (err)
            res.status(500).send('Database array error.');
        console.log(result);
        res.status(200).send(result);
    });

} catch (err) {
    console.log(err);
    res.status(500).send('Database error.');
}

EDIT:

The former answer is likely correct, the error I'm getting is due to using a uid instead of _id on the user collection (I believe):

Here is my attempt:

data.aggregate([
    {$unwind: "$history"},
    {"$lookup": {from: "users", localField: "history.userID", foreignField: "uid", as: "userLookup"}},
    {$unwind: "$userLookup"},
    {$project: {status: 1, history: {type: "$history.type", userName: {"$concat": ["$userLookup.firstname"," ","$userLookup.lastname"]}, date: "$history.date"}}},
    {$group: {_id: "$_id",status: {$first: "$status"}, history: {push: "$history"}}},
    {"$merge": {into: "process", on: "_id",whenMatched: "merge"}}
]).toArray(function(err, result) {
    console.log(result);
    if (err)
    {
        res.status(500).send('Database array error.');
        console.log(err);
    }
    else
        res.status(200).send(result);
});

User Document (now):

{
    _id: ObjectId('d6xMtHTIX3QO0FifUPgoJLOLz872'),
    uid: 'werwevmA5gZ2Ky2MUuSAj6TJiZz1',
    email: '[email protected]',
    firstname: 'Bobby',
    lastname: 'Tables',
}

CodePudding user response：

You can first $unwind the history array. Perform $lookup to the user collection. Build the new history object. Finally $group back into original form and $merge to update to the collection.

db.processes.aggregate([
  {
    $unwind: "$history"
  },
  {
    "$lookup": {
      from: "users",
      localField: "history.userID",
      foreignField: "_id",
      as: "userLookup"
    }
  },
  {
    $unwind: "$userLookup"
  },
  {
    $project: {
      status: 1,
      history: {
        type: "$history.type",
        userName: {
          "$concat": [
            "$userLookup.firstname",
            " ",
            "$userLookup.lastname"
          ]
        },
        date: "$history.date"
      }
    }
  },
  {
    $group: {
      _id: "$_id",
      status: {
        $first: "$status"
      },
      history: {
        $push: "$history"
      }
    }
  },
  {
    "$merge": {
      into: "process",
      on: "_id",
      whenMatched: "merge"
    }
  }
])

Mongo Playground