how to group based on id and a condition like date comparison between documents in MongoDB-CodePudding

In the following collection:

[
  {
    "p_id": "102",
    "e_date": ISODate("2017-04-01T00:00:00.000 00:00"),
    "h_val": 4,
    
  },
  {
    "p_id": "102",
    "e_date": ISODate("2005-04-01T00:00:00.000 00:00"),
    "h_val": 5,
    
  },
  {
    "p_id": "102",
    "e_date1": ISODate("2017-05-01T00:00:00.000 00:00"),
    "s_val": 87,
    "d_val": 58
  },
  {
    "p_id": "102",
    "e_date1": ISODate("2016-09-01T00:00:00.000 00:00"),
    "s_val": 81,
    "d_val": 62
  },
  {
    "p_id": "102",
    "e_date1": ISODate("2010-09-01T00:00:00.000 00:00"),
    "s_val": 81,
    "d_val": 62
  },
  {
    "p_id": "101",
    "e_date": ISODate("2016-04-01T00:00:00.000 00:00"),
    "h_val": 5,
    
  },
  {
    "p_id": "101",
    "e_date1": ISODate("2015-05-01T00:00:00.000 00:00"),
    "s_val": 87,
    "d_val": 58
  },
  
]

I want to compare the dates within each p_id and group those documents with e_date1 < e_date only.

I have tried the group aggregation and used a condition to find the e_date1 less than e_date but it just pushes either documents containing e_date1 or e-date. it does not bring up the corresponding documents. I am not sure if group would be the right solution for this.

{
"_id": {
  "pid": "$p_id",
  "date": {
    "$cond": [
      { "$lt": [ "$e_date1", "$e_date" ] },
      "edate1<edate",
      "edate<edate1",
    ]
  }
},
"records": {"$push": "$$ROOT"},
"count": { "$sum": 1 },
  }

CodePudding user response：

Since e_date1 and e_date are not on the same document, one option is to group and only then filter (during the grouping you still don't know what is the e_date value to compare to):

db.collection.aggregate([
  {$group: {
      _id: "$p_id",
      e_date: {$first: "$e_date"},
      records: {$push: "$$ROOT"}
  }},
  {$project: {
      p_id: "$_id", _id: 0, e_date:1, 
      records: {$filter: {
          input: "$records",
          cond: {
            $and: [
              {$gt: ["$$this.e_date1", null]},
              {$lt: ["$$this.e_date1", "$e_date"]}
            ]
          }
      }}
  }},
  {$addFields: {count: {$size: "$records"}}}
])

See how it works on the playground example

If you have many documents that should be filtered out and you don't want to group them all into one giant document, you can use the $setWindowFields before continuing along the guidelines of @user20042973.