含时间减法操作的MongoDB查询失效，返回JSON解析错误

Got it, let's break down why you're hitting this JSON parsing error when trying to run a date subtraction query in MongoDB. The error message points directly to a malformed date string somewhere in your query—MongoDB is super strict about date formats it can parse, so let's go through the most common culprits and fixes:

1. You're using a non-ISO 8601 date string without proper wrapping #

MongoDB expects dates to follow the ISO 8601 format (YYYY-MM-DDTHH:mm:ss.sssZ) and, in the mongo shell, to be wrapped with ISODate(). If you're passing a raw string like "2023-10-05 14:30" instead of a properly formatted date object, the parser will choke.

• ❌ Wrong:

  db.collection.aggregate([
    { $project: { timeDiff: { $subtract: [new Date(), "2023-10-05 12:00"] } } }
  ])
  

• ✅ Correct:

  db.collection.aggregate([
    { $project: { timeDiff: { $subtract: [new Date(), ISODate("2023-10-05T12:00:00Z")] } } }
  ])
  

2. You're trying to subtract strings instead of date objects #

If either operand in your $subtract operation is a string (not a date type), MongoDB can't perform the arithmetic and will throw a parsing error. Even if your string looks like a date, you need to convert it to a date first.

• ❌ Wrong:

  db.collection.find({
    $expr: { $lt: [{ $subtract: ["$createdAt", "7 days"] }, new Date()] }
  })
  

• ✅ Correct (use $toDate to convert strings, or use relative time calculations):

  db.collection.find({
    $expr: { $lt: [{ $subtract: ["$createdAt", ISODate("2023-09-28T00:00:00Z")] }, new Date()] }
  })
  // Or use $add with negative values for relative time (7 days in milliseconds)
  db.collection.find({
    $expr: { $lt: ["$createdAt", { $add: [new Date(), -7*24*60*60*1000] }] }
  })
  

3. Your date string has invalid characters or values #

Double-check the date at line 1, column 83 (as mentioned in the error) for common mistakes:

• Missing the T separator between date and time (e.g., "2023-10-05 12:00" instead of "2023-10-05T12:00")
• Invalid month/day values (e.g., "2023-13-05" for a non-existent 13th month, or "2023-02-30" which doesn't exist in February)
• Wrong timezone format (e.g., using +0800 instead of Z for UTC, or missing the timezone entirely)
• Non-ASCII characters (like localized date separators or month names)

4. Driver-specific date formatting issues #

If you're using a programming language driver (Node.js, Python, Java) instead of the mongo shell, make sure you're passing actual date objects, not strings. For example:

• In Node.js: Use new Date("2023-10-05T12:00:00Z") instead of the raw string.
• In Python: Use datetime.datetime(2023,10,5,12,0,tzinfo=datetime.timezone.utc) instead of a string representation.

Drivers will automatically convert these objects to MongoDB's BSON date type, which avoids parsing errors entirely.

Quick Troubleshooting Tip #

Copy your query string and count to column 83 (remember, columns start at 1) to pinpoint exactly which date is causing the issue. That will help you narrow down whether it's a formatting typo or an invalid date value.

内容的提问来源于stack exchange，提问作者bndfcom