MongoDB Aggregate two collections and flatten the second collection

Question

Collection - 1 (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"   
}

Collection -2 (2 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"    
}

Expected Result:

{
    "_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"
     
}

Query Used:

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

score 1 · Answer 1 · answered Aug 04 '22 at 07:22

One option is using $reduce with $arrayToObject:

db.users.aggregate([
  {$match: {_id: "aandtv10@gmail.com"}},
  {$set: {data: "$$ROOT"}},
  {$lookup: {
      from: "documents",
      localField: "_id",
      foreignField: "email",
      as: "documents"
    }
  },
  {$project: {
      _id: 0,
      data: 1,
      documents: {
        $reduce: {
          input: "$documents",
          initialValue: [],
          in: {
            $concatArrays: [
              "$$value",
              [
                {k: {$concat: ["$$this.docType", "_administeredTimestamp"]},
                  v: "$$this.administeredTimestamp"},
                {k: {$concat: ["$$this.docType", "_manufacturer"]},
                  v: "$$this.manufacturer"}
              ]
            ]
          }
        }
      }
    }
  },
  {$replaceRoot: {newRoot: {$mergeObjects: ["$data", {$arrayToObject: "$documents"}]}}}
])

See how it works on the playground example

we cannot use $set as it is not supported by aws document db. — mperle, Aug 04 '22 at 17:50

MongoDB Aggregate two collections and flatten the second collection

1 Answers1