get difference between two dates in $project

Question

Trying to project the date difference between two dates, but I am getting error -

Invalid $project :: caused by :: Unknown expression $dateDiff

db.books.aggregate([{
$project:{
  Date_diff: 
    {$dateDiff:{
      start_dt:'$borrowers_list.borrowed_dt',
      endDate:'$borrowers_list.return_dt',
      unit: "day"
    }
  }
}
}])

The json document structure is like this -

_id:6188a5283543f7cc2f77c73f
branch_id:1
    borrowers_list:Object    
        0:Object
            borrowed_dt:2021-08-15T06:00:00.000+00:00
            card_no:"ID000067"
            return_dt:2021-08-25T06:00:00.000+00:00

I have no idea why the error is unknown expression $dateDiff, as my syntax is correct. Does anyone have any suggestions?

Answer 1

Based on your provided JSON document, the document should be as below (correct me if it is incorrect):

  {
    _id: ObjectId("6188a5283543f7cc2f77c73f"),
    branch_id: 1,
    borrowers_list: {
      0: {
        borrowed_dt: ISODate("2021-08-15T06:00:00.000+00:00"),
        card_no: "ID000067",
        return_dt: ISODate("2021-08-25T06:00:00.000+00:00")
      }
    }
  }
]

There is no start_dt in $dateFiff field, it is startDate.

Query

db.collection.aggregate([
  {
    $project: {
      Date_diff: {
        $dateDiff: {
          startDate: "$borrowers_list.0.borrowed_dt",
          endDate: "$borrowers_list.0.return_dt",
          unit: "day"
        }
      }
    }
  }
])

Note: Above query will perform the $dateDiff for the first document in borrowers_list.

Sample Mongo Playground

---

In case you need to iterate each document (with key-value pair) in borrowers_list to perform $dateDiff.

1. $set - Convert from object to array (via $objectToArray) for borrowers_list to new field borrowers.
2. $set - Iterate each document in borrowers array (1) and perform $dateDiff.
3. $project - Decorate the output document, convert Date_diff from array to object (via $objectToArray).

Query

db.collection.aggregate([
  {
    $set: {
      borrowers: {
        "$objectToArray": "$borrowers_list"
      }
    }
  },
  {
    $set: {
      Date_diff: {
        $map: {
          input: "$borrowers",
          as: "b",
          in: {
            k: "$$b.k",
            v: {
              $dateDiff: {
                startDate: "$$b.v.borrowed_dt",
                endDate: "$$b.v.return_dt",
                unit: "day"
              }
            }
          }
        }
      }
    }
  },
  {
    $project: {
      Date_diff: {
        "$arrayToObject": "$Date_diff"
      }
    }
  }
])

Sample Mongo Playground (Iterate document with key-value pair)