Below is the array

 {
    "CDF": {
        "UTILITYTYPE": {
            "D1": {
                "G1": "12387835",
                "G22": {
                    "NAME": "L1"
                }
            },
            "D5": {
                "EVENT": [
                    {
                        "CODE": "13",
                        "TIME": "29-05-2022 13:26:00",
                        "STATUS": "0"
                    },
                    {
                        "CODE": "13",
                        "TIME": "29-05-2022 14:41:00",
                        "STATUS": "1"
                    },
                    {
                        "CODE": "13",
                        "TIME": "31-05-2022 10:13:00",
                        "STATUS": "0"
                    },
                    {
                        "CODE": "13",
                        "TIME": "31-05-2022 10:18:00",
                        "STATUS": "1"
                    }
                ]
            }
        }
    }
},
{
    "CDF": {
        "UTILITYTYPE": {
            "D1": {
                "G1": "12388215",
                "G22": {
                    "NAME": "L2"
                }
            },
            "D5": {
                "EVENT": [
                    {
                        "CODE": "7",
                        "TIME": "16-05-2022 04:28:21",
                        "STATUS": "0"
                    },
                    {
                        "CODE": "7",
                        "TIME": "16-05-2022 06:30:30",
                        "STATUS": "1"
                    },
                    {
                        "CODE": "7",
                        "TIME": "16-05-2022 07:36:53",
                        "STATUS": "0"
                    },
                    {
                        "CODE": "7",
                        "TIME": "16-05-2022 19:39:28",
                        "STATUS": "1"
                    }
                ]
            }
        }
    }
}

How do I compare below time to get difference using aggregation in mongodb? The comparison should be between the first and the second then third and fourth and further. Also I want to convert the dates in proper comparable date format. Expected output is

i want to get the record whos TIME difference between 2 events is more than 1 hours

Eg: Suppose date difference between these 2 subdocuments in first record has difference of more than 1 hour then i should only get G1:12387835 RECORD IN THE OUT PUT ARRAY

{
    "CODE": "13",
    "TIME": "29-05-2022 13:26:00",
    "STATUS": "0"
},
{
    "CODE": "13",
    "TIME": "29-05-2022 14:41:00",
    "STATUS": "1"
}

CodePudding user response：

One option is:

1. Format the time
2. Divide the events into to arrays according to the status
3. Merge them back as couples.
4. Keep only couples with time difference greater than 1 hour

db.collection.aggregate([
  {
    $project: {
      EVENT: {
        $map: {
          input: "$CDF.UTILITYTYPE.D5.EVENT",
          as: "i",
          in: {
            CODE: "$$i.CODE",
            STATUS: "$$i.STATUS",
            TIME: {
              $dateFromString: {
                "dateString": "$$i.TIME",
                "format": "%d-%m-%Y %H:%M:%S"
              }
            }
          }
        }
      }
    }
  },
  {
    $project: {
      firstEvent: {
        $filter: {input: "$EVENT", cond: {$eq: ["$$this.STATUS", "0"]}}}
      },
      secondEvent: {
        $filter: {input: "$EVENT", cond: {$eq: ["$$this.STATUS", "1"]}}}
      }
    }
  },
  {$project: {couples: {$zip: {inputs: ["$firstEvent", "$secondEvent"]}}}},
  {$project: {
      couples: {
        $filter: {
          input: "$couples",
          cond: {
            $gt: [
              {$dateDiff: {
                  startDate: {$first: "$$this.TIME"},
                  endDate: {$last: "$$this.TIME"},
                  unit: "hour"
                }
              }, hourDiffParameter]
          }
        }
      }
    }
  }
])

See how it works on the playground example