1

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"
}
Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
Aksheet-Goku
  • 71
  • 7

1 Answers1

0

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

nimrod serok
  • 14,151
  • 2
  • 11
  • 33
  • and suppose i want the time difference should be of more than 2 hours ? – Aksheet-Goku Jul 01 '22 at 11:05
  • Just change the value for `1` to `2` – nimrod serok Jul 01 '22 at 11:07
  • hourDiffParameter – nimrod serok Jul 01 '22 at 11:08
  • how do i remove the documents which does not match Example: "couples": [] in the https://mongoplayground.net/p/6Y2xXNLonb1 example so i want to remove complete document from the result also i want to add look up from another collection example { $lookup: { from: "dumpMaster", localField: "CDF.UTILITYTYPE.D1.G1", foreignField: "MET_SR", as: "CONDETAILS" } } – Aksheet-Goku Jul 04 '22 at 06:25
  • Try [this](https://mongoplayground.net/p/4r2Giebit1Q) – nimrod serok Jul 04 '22 at 06:54
  • it is not working in my case any other solution for this ? this is the error on connection: connection to 127.0.0.1:27017 : aggregate failed : _getErrorWithCode@src/mongo/shell/utils.js:25:13 doassert@src/mongo/shell/assert.js:18:14 _assertCommandWorked@src/mongo/shell/assert.js:719:17 assert.commandWorked@src/mongo/shell/assert.js:811:16 DB.prototype._runAggregate@src/mongo/shell/db.js:276:5 DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1058:12 – Aksheet-Goku Jul 04 '22 at 07:38
  • Did you copy it right? – nimrod serok Jul 04 '22 at 08:27
  • i am getting this error MongoServerError: PlanExecutor error during aggregation :: caused by :: The argument to $size must be an array, but was of type: null – Aksheet-Goku Jul 04 '22 at 08:29
  • 1
    Try [this](https://mongoplayground.net/p/jDX2r6gy3pY) then – nimrod serok Jul 04 '22 at 09:04
  • How can i get the difference time value in this @nimrod serok – Aksheet-Goku Jul 05 '22 at 08:05
  • Same as before. Change line 78 in the link – nimrod serok Jul 05 '22 at 10:06
  • i want the time example ("TIME": "29-05-2022 13:26:00" - "TIME": "29-05-2022 14:41:00") = 10 hours – Aksheet-Goku Jul 05 '22 at 10:23
  • This is a new expected output, you should ask a new question and define the expected output in there. – nimrod serok Jul 05 '22 at 10:25
  • can you help me with this issue https://stackoverflow.com/questions/72882782/how-to-get-results-whos-condition-matches-in-subdocuments-in-mongodb – Aksheet-Goku Jul 06 '22 at 12:19
  • can you help me in this issue https://stackoverflow.com/questions/72894200/how-to-retrive-only-matched-subdocuments-in-mongodb-aggregation – Aksheet-Goku Jul 08 '22 at 04:55