0

I have a scenario where I need to get all the documents where "CreatedDatetime" should greater than "Deal.SourceStartDatetime" from same collection.

Example : Below is the 2 documents from same collection.

{
    "DealNumber": "AAAAAA",
    "CreatedDatetime": "2021-01-25T21:14:39.670Z",
    "BatchIdentifier": "ebe4d466-77777-4b8b-b965-22ebf04de85e",
    "EventName": "DEAL_Insert",
    "LastUpdateDatetime": "2021-01-25T21:14:39.670Z",
    "MessageIdentifier": "5fcfb89d-3cf6-57e4-9d5c-e0141700852b",
    "SourceDeleteIndicator": "N",
    "TransactionIdentifier": "a80cdc54-12d7-434e-bc03-5d9c2b75b1f8",
    "DealLastUpdateDatetime": "2021-01-20T17:51:36.644Z",
    "Deal": {
        "AgreementName": "xxxx",
        "SourceEndDatetime": "2021-01-20T17:51:36.644Z"
        "SourceStartDatetime": "2021-01-20T17:51:36.644Z"        
    }
}

{
    "DealNumber": "BBBBBB",
    "CreatedDatetime": "2021-01-20T17:51:36.644Z",
    "BatchIdentifier": "ebe4d466-77777-4b8b-b965-22ebf04de85e",
    "EventName": "DEAL_Insert",
    "LastUpdateDatetime": "2021-01-25T21:14:39.670Z",
    "MessageIdentifier": "5fcfb89d-3cf6-57e4-9d5c-e0141700852b",
    "SourceDeleteIndicator": "N",
    "TransactionIdentifier": "a80cdc54-12d7-434e-bc03-5d9c2b75b1f8",
    "DealLastUpdateDatetime": "2021-01-20T17:51:36.644Z",
    "Deal": {
        "AgreementName": "xxxx",
        "SourceEndDatetime": "2021-01-20T17:51:36.644Z"
        "SourceStartDatetime": "2021-01-20T17:51:36.644Z"        
    }
}
varman
  • 8,704
  • 5
  • 19
  • 53
Sam
  • 1
  • 1
  • Can https://stackoverflow.com/questions/4442453/mongodb-query-condition-on-comparing-2-fields help? – ray Feb 01 '21 at 02:45

1 Answers1

0

You can use $expr and $gt

db.collection.find({
  $expr: {
    $gt: [
      {$toDate: "$CreatedDatetime"},
      {$toDate: "$Deal.SourceStartDatetime"}
    ]
  }
})

Working Mongo playground

varman
  • 8,704
  • 5
  • 19
  • 53