How to join multiple documents in a MongoDB query-CodePudding

I am new to MongoDB. I have worked on Legacy Mainframe and DB2 Database. My requirement is as below - I have a collection say Logs which has fields named as CaseNum, ProcessingStage, body1 and body2. The Documents in collection looks like this

Doc1 - CaseNum: '123', ProcessingStage: 'Start', body1: {data object1}
Doc2 - CaseNum: '123', ProcessingStage: 'Error', body2: {data object2}
Doc3 - CaseNum: '123', ProcessingStage: 'Success', body2: {data object2}
Doc4 - CaseNum: '456', ProcessingStage: 'Start', body1: {data object3}
Doc5 - CaseNum: '456', ProcessingStage: 'Error', body2: {data object4}
Doc6 - CaseNum: '789', ProcessingStage: 'Start', body1: {data object5}

I need to extract body1 field from the document having ProcessingStage as 'Start' where the CaseNum has a 'Error' document and does not have the 'Success' document. So in this example, the MongoDB query should fetch body1 from Doc4 of CaseNum '456' because it has a 'Error' document Doc5 and does not have the 'Success' document.
CaseNum '123' has an 'Error' document but since it also has a 'Success' document so it fails the desired condition and CaseNum '789' does not have the 'Error' document so it also fails the desired condition.

My apologies, If I am not able to explain my issue properly. Please help.

CodePudding user response：

To merge multiple documents in MongoDB, use aggregate(). Let's say we have a document named demoDocument:

{ "_id" : "101", "Name" : "Chris", "details" : [ { "CountryName" : "US", "Age" : 21 } ], "Price" : 50 }
{ "_id" : "102", "Name" : "Chris", "details" : [ { "CountryName" : "UK", "Age" : 22 } ], "Price" : 78 }
{ "_id" : "103", "Name" : "Chris", "details" : [ { "CountryName" : "US", "Age" : 21 } ], "Price" : 50 }

Following is the query to merge multiple documents in MongoDB −

> db.demo436.aggregate([
       {$sort: {_id: 1, Name: 1}},
       {$unwind: '$details'},
       {$group: {_id: '$Name', details: {$push: '$details'},
       Price: {$sum: '$Price'},
       id: {$last: {$concat: ["$_id", "_", "AppendedValue" ]}},
       Name: {$last: '$Name'}}},
       {$addFields: {Id: 'NewIdAppped', _id: '$id'}},
       {$project: {"id": 0 }}])

Doing this will produce following output:

{
    "_id": "103_AppendedValue",
    "details": [{
        "CountryName": "US",
        "Age": 21
    }, {
        "CountryName": "UK",
        "Age": 22
    }, {
        "CountryName": "US",
        "Age": 21
    }],
    "Price": 178,
    "Name": "Chris",
    "Id": "NewIdAppped"
}

Hope you got the answer, what you were looking for.

CodePudding user response：

You need to use aggregation to achieve this. Here's one possible solution

db.collection.aggregate([
 // group all docs by case num
 {
  $group: {
    _id: "$CaseNum",
    processingStages: {
      $push: "$ProcessingStage"
    },
   // only get body 1 from start stage
   body1: {
    $push: {
      $cond: [
        {
          $eq: [
            "$ProcessingStage",
            "Start"
          ]
        },
        "$body1",
        "$$REMOVE"
      ]
    }
  }
 }
},
// check your condition,
// case shouldn't have success and must have error
{
 $match: {
  $and: [
    {
      processingStages: {
        $in: [
          "Error"
        ]
      }
     },
     {
       processingStages: {
        $nin: [
          "Success"
        ]
      }
    }
  ]
 }
},
// extract body1 as object
{
  "$unwind": "$body1"
}])

Here's what your result would look like

[
 {
  "_id": "456",
  "body1": {
   "data": {}
  },
  "processingStages": [
    "Start",
    "Error"
  ]
 }
]

Playground link: https://mongoplayground.net/p/RRQax-S2WbA