CODEWITHSUNDEEP
node.jsmongodbmongoose
GeekyCode
GeekyCode

Reputation: 265

Mongodb/Mongoose - Aggregation to group all matching docs in same collection in an array

I am in a scenario where I want to find some similar documents and group them in an array. Here is an example to illustrate better the idea:

Documents in collection:

{
   _id: 0,
   enabled: false,
   name:"name A",
   amounts:{ salary: 100 },
   field_condition: "No",
   created_A: "2020-07-01"
}

{
   _id: 1,
   enabled: false,
   name:"name A",
   amounts:{ salary: 100 },
   field_condition: "Yes",
   created_A: "2020-08-01"
}

{
   _id: 2,
   enabled: false,
   name:"name A",
   amounts:{ salary: 100 }, 
   field_condition: "Yes",
   created_A: "2020-09-01"
}

{
   _id: 3,
   enabled: true,
   name:"name A",
   amounts:{ salary: 100 }, 
   field_condition: "No",
   created_A: "2020-10-01"
}



{
   _id: 4,
   enabled: false,
   name:"name B",
   amounts:{ salary: 100 },
   field_condition: "No",
   created_A: "2020-07-01"
}

{
   _id: 5,
   enabled: false,
   name:"name B",
   amounts:{ salary: 100 },
   field_condition: "Yes",
   created_A: "2020-08-01"
}

{
   _id: 6,
   enabled: false,
   name:"name B",
   amounts:{ salary: 100 }, 
   field_condition: "Yes",
   created_A: "2020-09-01",

}

{
   _id: 7,
   enabled: true,
   name:"name B",
   amounts:{ salary: 100 }, 
   field_condition: "No",
   created_A: "2020-10-01"
}

I want to find all documents where the enabled field is trueand field_condition is equal to No that would result to these two documents:

{
   _id: 3,
   enabled: true,
   name:"name A",
   amounts:{ salary: 100 }, 
   field_condition: "No",
   created_A: "2020-10-01"
}

{
   _id: 7,
   enabled: true,
   name:"name B",
   amounts:{ salary: 100 }, 
   field_condition: "No",
   created_At: "2020-10-01"
}

Then, I want to recursively find the documents of the previous months, that match the name field, enabled: false and field_condition: "Yes" till I reach one where field_condition: "No" and stop. Then I want to put the found documents of previous month, in an array of the actual document:

[ 
 {
   _id: 3,
   enabled: true,
   name:"name A",
   amounts:{ salary: 100 }, 
   field_condition: "No",
   created_A: "2020-10-01",
   relatedDocs: [
      {
         _id: 1,
         enabled: false,
         name:"name A",
         amounts:{ salary: 100 },
         field_condition: "Yes",
         created_A: "2020-08-01"
      },

      {
         _id: 2,
         enabled: false,
         name:"name A",
         amounts:{ salary: 100 }, 
         field_condition: "Yes",
         created_A: "2020-09-01"
      }
   ]
},


{
   _id: 7,
   enabled: true,
   name:"name B",
   amounts:{ salary: 100 }, 
   field_condition: "No",
   created_At: "2020-10-01",
   relatedDocs: [
      {
         _id: 1,
         enabled: false,
         name:"name B",
         amounts:{ salary: 100 },
         field_condition: "Yes",
         created_A: "2020-08-01"
      },

      {
         _id: 2,
         enabled: false,
         name:"name B",
         amounts:{ salary: 100 }, 
         field_condition: "Yes",
         created_A: "2020-09-01"
      }
   ]
 }
]

I tried so hard with the $lookupaggregation but could not get it to work. Your help is much appreciated . Thanks!

Upvotes: 0

Views: 204

Answers (1)

Gibbs
Gibbs

Reputation: 22964

It is not straight forward to achieve.

play

You can have entire data sorted with the desired order.

db.collection.aggregate([
  {
    "$addFields": {//add a field to sort
      "consider": {
        $and: [
          {
            "$eq": [
              "$enabled",
              true
            ]
          },
          {
            $eq: [
              "$field_condition",
              "No"
            ]
          }
        ]
      },
      "date": {
        "$dateFromString": {
          "dateString": "$created_A"
        }
      }
    }
  },
  {
    $sort: { //sort the data
      "consider": -1,
      "date": -1
    }
  },
  {
    $group: {//group by name and data will have sorted result
      "_id": "$name",
      "data": {
        $push: "$$ROOT"
      },
      "considerDoc": {
        $first: "$$ROOT"
      }
    }
  }
])

If you are sure that every month will have an entry and only latest months will have Yes in that field condition, then you can filter it in another stage.

Otherwise you have to do it in the backend code.

Upvotes: 1

Related Questions