MongoDB多集合聚合并扁平化第二个集合的技术实现问询

需求概述 #

实现MongoDB中users和documents两个集合的聚合操作，将documents集合的数据按docType扁平化到用户文档中，得到指定的期望结果。

集合结构 #

users集合（单文档） #

{
    "_id" : "aandtv10@gmail.com",
    "updateTimestamp" : "2022-07-19T11:59:18.029Z",
    "userConsent" : null,    
     "firstName" : "Aand",
     "lastName" : "Aa",
     "fullName" : "Aand Aa", 
     "orgRootName" : "Black&Veatch",
     "relationType" : "Contractor"   
}

documents集合（两个文档） #

/* 1 */
{
    "_id" : ObjectId("62e9296bf06b3bfdc93be0da"),
    "lastUpdated" : "2022-08-02T13:40:59.265Z",
    "email" : "aandtv10@gmail.com",
    "userName" : "Aand Aa",    
    "docType" : "dose2",
    "administeredTimestamp" : "2022-10-02T11:10:55.784Z",
    "uploadTimestamp" : "2022-08-02T13:40:59.265Z",
    "manufacturer" : "Novavax"    
}
/* 2 */
{
    "_id" : ObjectId("62e911cf5b6e67c36c4f843e"),
    "lastUpdated" : "2022-08-02T12:00:15.201Z",
    "email" : "aandtv10@gmail.com",
    "userName" : "Aand Aa",
    "templateName" : "Covid-Vaccine",
    "docType" : "dose1",
    "exemptType" : null,
    "administeredTimestamp" : "2022-08-23T17:16:20.347Z",
    "uploadTimestamp" : "2022-08-02T12:00:15.202Z",
    "manufacturer" : "Novavax"    
}

期望结果 #

{
    "_id" : "aandtv10@gmail.com",
    "updateTimestamp" : "2022-07-19T11:59:18.029Z",    
     "firstName" : "Aand",
     "lastName" : "Aa",
     "fullName" : "Aand Aa", 
     "orgRootName" : "Black&Veatch",
     "relationType" : "Contractor",
     "dose1_administeredTimestamp" : "2022-08-23T17:16:20.347Z",
     "dose1_manufacturer" : "Novavax",
     "dose2_administeredTimestamp" : "2022-10-02T11:10:55.784Z",
     "dose2_manufacturer" : "Novavax"
}

当前查询语句 #

db.users.aggregate([{
    "$match": {
        "_id": "aandtv10@gmail.com"
        }
    },{
        $lookup: {
            from: "documents",
            localField: "_id",
            foreignField: "email",
            as: "documents"
        }}, { $unwind: "$documents" },{
            "$group": {
                "fullName": { $max: "$identity.fullName" },
                "_id":"$_id",
                "relationship": { $max:"$seedNetwork.relationType" },
                "registration_date": { $max:"$seedNetwork.seededTimestamp" },
                "vaccination_level": { $max: "" },
                "exemption_declination_date": { $max:"N/A" },
                "exemption_verification": { $max: "N/A" },
                                "dose1_date" : { $max: { $cond: { if: {$eq: ["$docType", "dose1" ]}, then: "$administeredTimestamp", else: "<false-case>" } } }
                
            }
        }
    
]);

完善后的聚合查询 #

db.users.aggregate([
    // 匹配目标用户
    { "$match": { "_id": "aandtv10@gmail.com" } },
    // 关联documents集合
    {
        "$lookup": {
            from: "documents",
            localField: "_id",
            foreignField: "email",
            as: "documents"
        }
    },
    // 处理关联后的数组，保留无文档的用户
    { "$unwind": { path: "$documents", preserveNullAndEmptyArrays: true } },
    // 按用户ID分组，聚合dose1和dose2的字段
    {
        "$group": {
            "_id": "$_id",
            "updateTimestamp": { "$first": "$updateTimestamp" },
            "firstName": { "$first": "$firstName" },
            "lastName": { "$first": "$lastName" },
            "fullName": { "$first": "$fullName" },
            "orgRootName": { "$first": "$orgRootName" },
            "relationType": { "$first": "$relationType" },
            // 聚合dose1的接种时间
            "dose1_administeredTimestamp": {
                "$max": {
                    "$cond": [
                        { "$eq": ["$documents.docType", "dose1"] },
                        "$documents.administeredTimestamp",
                        null
                    ]
                }
            },
            // 聚合dose1的疫苗厂商
            "dose1_manufacturer": {
                "$max": {
                    "$cond": [
                        { "$eq": ["$documents.docType", "dose1"] },
                        "$documents.manufacturer",
                        null
                    ]
                }
            },
            // 聚合dose2的接种时间
            "dose2_administeredTimestamp": {
                "$max": {
                    "$cond": [
                        { "$eq": ["$documents.docType", "dose2"] },
                        "$documents.administeredTimestamp",
                        null
                    ]
                }
            },
            // 聚合dose2的疫苗厂商
            "dose2_manufacturer": {
                "$max": {
                    "$cond": [
                        { "$eq": ["$documents.docType", "dose2"] },
                        "$documents.manufacturer",
                        null
                    ]
                }
            }
        }
    },
    // 处理空值，确保结果格式统一
    {
        "$project": {
            "_id": 1,
            "updateTimestamp": 1,
            "firstName": 1,
            "lastName": 1,
            "fullName": 1,
            "orgRootName": 1,
            "relationType": 1,
            "dose1_administeredTimestamp": { "$ifNull": ["$dose1_administeredTimestamp", ""] },
            "dose1_manufacturer": { "$ifNull": ["$dose1_manufacturer", ""] },
            "dose2_administeredTimestamp": { "$ifNull": ["$dose2_administeredTimestamp", ""] },
            "dose2_manufacturer": { "$ifNull": ["$dose2_manufacturer", ""] }
        }
    }
]);

关键修改说明 #

• 修正分组阶段的字段引用：原查询使用了不存在的$identity.fullName、$seedNetwork.relationType字段，改为直接引用users集合的原生字段。
• 在$unwind阶段添加preserveNullAndEmptyArrays: true，确保用户无关联文档时仍能返回结果。
• 通过$cond结合$max，分别聚合dose1和dose2对应的字段，实现数据扁平化。
• 添加$project阶段处理空值，保证结果格式与预期一致。

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