MongoDB嵌套userID替换为用户名的聚合查询实现问询

需求说明 #

现有两个MongoDB集合processes和users，使用Node.js原生MongoDB驱动查询processes文档时，需要将history嵌套数组中的userID字段替换为对应用户的全名（firstname + lastname）。最初尝试聚合查询仅能替换单个记录，后续发现users集合实际使用uid而非_id作为关联字段，调整语句后仍未解决问题，需正确实现批量替换逻辑。

集合文档示例 #

Process 文档 #

{
    _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 文档（初始示例） #

// User 1
{
    _id: ObjectId('d6xMtHTIX3QO0FifUPgoJLOLz872'),
    email: 'something@something.com',
    firstname: 'Bobby',
    lastname: 'Tables',
}

// User 2
{
    _id: ObjectId('61e77cdedde2dbe1cbf8a250'),
    email: 'something2@something.com',
    firstname: 'Jenny',
    lastname: 'Tables',
}

当前User文档（补充） #

{
    _id: ObjectId('d6xMtHTIX3QO0FifUPgoJLOLz872'),
    uid: 'werwevmA5gZ2Ky2MUuSAj6TJiZz1',
    email: 'something@something.com',
    firstname: 'Bobby',
    lastname: 'Tables',
}

期望输出 #

{
    _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)'
        }
    ]
}

已尝试的代码 #

初始尝试（仅能替换单个记录） #

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.');
}

调整关联字段后的尝试（仍有问题） #

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

正确解决方案 #

聚合查询逻辑说明 #

1. $lookup关联用户数据：一次性关联所有涉及的用户，避免多次查询。
2. $map遍历history数组：将每个history项的userID匹配到对应的用户全名，替换为userName并移除原userID。
3. 保留原文档字段：确保_id、status等字段保留，仅修改history数组。

最终聚合代码 #

try {
    const db = mongo.getDB();
    const processesCollection = db.collection("processes");
    
    const result = await processesCollection.aggregate([
        // 关联users集合，匹配history.userID与user.uid
        {
            $lookup: {
                from: "users",
                localField: "history.userID",
                foreignField: "uid",
                as: "matchedUsers"
            }
        },
        // 处理history数组，替换userID为userName
        {
            $addFields: {
                history: {
                    $map: {
                        input: "$history",
                        as: "item",
                        in: {
                            type: "$$item.type",
                            date: "$$item.date",
                            userName: {
                                $let: {
                                    vars: {
                                        matchedUser: {
                                            $first: {
                                                $filter: {
                                                    input: "$matchedUsers",
                                                    cond: { $eq: ["$$this.uid", "$$item.userID"] }
                                                }
                                            }
                                        }
                                    },
                                    in: { $concat: ["$$matchedUser.firstname", " ", "$$matchedUser.lastname"] }
                                }
                            }
                        }
                    }
                },
                // 移除临时的matchedUsers字段
                matchedUsers: "$$REMOVE"
            }
        },
        // 保留需要的字段
        {
            $project: {
                _id: 1,
                status: 1,
                history: 1
            }
        }
    ]).toArray();

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

关键注意事项 #

• 确保processes集合中history.userID与users集合的uid完全匹配（类型一致，无格式差异）。
• 使用await简化异步逻辑，避免回调嵌套（Node.js环境需确保函数为async）。
• 如果不需要修改原集合，移除$merge阶段，仅返回查询结果即可。

内容的提问来源于stack exchange，提问作者supremesector