I am trying to compare dates in elements of an array. Here is a sample of my collection:

[
  {
    "_id": "102",
    "records": [
      {
        "p_id": "102",
        "e_date": ISODate("2016-04-01T00:00:00.000 00:00"),
        "h_val": 4,
        
      },
      {
        "p_id": "102",
        "e_date": ISODate("2017-04-01T00:00:00.000 00:00"),
        "h_val": 5,
        
      },
      {
        "p_id": "102",
        "e_date": ISODate("2018-04-01T00:00:00.000 00:00"),
        "h_val": 6,
        
      },
      {
        "p_id": "102",
        "e_date_1": ISODate("2003-05-01T00:00:00.000 00:00"),
        "s_val": 87,
        "d_val": 58
      },
      {
        "p_id": "102",
        "e_date_1": ISODate("2004-09-01T00:00:00.000 00:00"),
        "s_val": 81,
        "d_val": 62
      },
      {
        "p_id": "102",
        "e_date_1": ISODate("2005-09-01T00:00:00.000 00:00"),
        "s_val": 81,
        "d_val": 62
      }
    ]
  }
]

In the above collection I want to compare the objects with e_date to e_date_1. If the dateDiff be greater than (for ex. 13) then pair the elements and create a new array of objects or just pair them in any way that is possible. I will have variable number of e_date and e_date_1 in each array (I will not have 3 elements with e_date and 3 elements with e_date_1)

I have been able to do a permutation by using $map function and check if the elements are greater than 13 or not using the below aggregate:

db.collection.aggregate([
  {
    $project: {
      //_id: 0,
      yearDiff: {
        $map: {
          input: "$records.e_date",
          as: "ed",
          in: {
            $map: {
              input: "$records.e_date_1",
              as: "ed1",
              in: {
                $gte: [
                  {
                    $abs: {
                      $dateDiff: {
                        "startDate": "$$ed",
                        "endDate": "$$ed1",
                        "unit": "year"
                      }
                    }
                  },
                  13
                ]
              }
            }
          }
        }
      }
    }
  }
])

the result is:

[
  {
    "_id": "102",
    "yearDiff": [
      [
        true,
        false,
        false
      ],
      [
        true,
        true,
        false
      ],
      [
        true,
        true,
        true
      ]
    ]
  }
]

but I want to be able to pair the objects that satisfy the condition and my desired output will be like:

[
      {
        "p_id": "102",
        "e_date": ISODate("2016-04-01T00:00:00.000 00:00"),
        "h_val": 4,
        
      },
      {
        "p_id": "102",
        "e_date_1": ISODate("2003-05-01T00:00:00.000 00:00"),
        "s_val": 87,
        "d_val": 58
      },
],
[
      {
        "p_id": "102",
        "e_date": ISODate("2017-04-01T00:00:00.000 00:00"),
        "h_val": 5,
      },

     {
        "p_id": "102",
        "e_date_1": ISODate("2003-05-01T00:00:00.000 00:00"),
        "s_val": 87,
        "d_val": 58
      },
      {
        "p_id": "102",
        "e_date_1": ISODate("2004-09-01T00:00:00.000 00:00"),
        "s_val": 81,
        "d_val": 62
      },
]

MongoDB playground: https://mongoplayground.net/p/N775HtMH4ST

CodePudding user response：

Maybe you want something like this, which is close to what you did, but with few simple adjustments:

1. use $filter for the external $map input, so we can only iterate on objects with e_date and can keep the entire object
2. Replace the internal $map with $filter to keep only matching objects, and again, keep the entire object.
3. Use $concatArrays to add the external $map object to the internal $filter results, as you requested.

db.collection.aggregate([
  {$project: {
      //_id: 0,
      yearDiff: {$map: {
          input: {$filter: {input: "$records", cond: {$gt: ["$$this.e_date", 0]}}},
          as: "ed",
          in: {$concatArrays: [
              ["$$ed"],
              {$filter: {
                  input: "$records",
                  cond: {
                    $gte: [
                      {$abs: {
                          $dateDiff: {
                            startDate: "$$ed.e_date",
                            endDate: "$$this.e_date_1",
                            unit: "year"
                          }
                      }},
                      13
                    ]
                  }
              }}
            ]
          }
      }}
  }}
])

See how it works on the playground example