MongoDB报错：无法将BSON字符串类型转为Date类型求助

尝试通过URL参数过滤MongoDB存储的数据时，服务器崩溃并报错：can't convert from BSON type string to Date，已尝试将参数转为Number但问题依旧。

前端请求代码 #

async filter(){
        month = this.$refs.month.value;
        year = this.$refs.year.value;

        if(month != "Month" && year != ""){
          const addressToFetch = "/api/budget/" + year + "/" + month;
          console.log("Fetching" + addressToFetch);
          await fetch(addressToFetch).then(response => response.json())
      .then(response => {
          this.expenses = response;
          console.log(response);
      });

        }
    }

该函数通过点击HTML表单按钮触发，获取两个输入框的年月值并发送请求。

后端处理代码 #

app.get("/api/budget/:year/:month", verify, async (req, res) => {
  const user = req.session.user;

  const year = Number(req.params.year);
  const month = Number(req.params.month);

  console.log("Got " + year + "/" + month);

  const expenses = db
    .collection("expenses")
    .find({
      "users.username": user.username,
      $expr: {
        $and: [
          { $eq: [{ $year: "$date" }, year] },
          { $eq: [{ $month: "$date" }, month] },
        ],
      },
    })
    .toArray();

  console.log(expenses);
  res.json(expenses);
});

数据库示例文档 #

[
    {
        "_id": "6571eb70320499f416931fd8",
        "id": 2,
        "date": "2021-09-06T00:00:00.000Z",
        "description": "boringday",
        "category": "transport",
        "buyer": "giacomo",
        "cost": 49,
        "users": [
            {
                "username": "paolo",
                "amount": 16.333333333333332
            },
            {
                "username": "giacomo",
                "amount": 16.333333333333332
            },
            {
                "username": "giacomo",
                "amount": 16.333333333333332
            }
        ]
    },
    {
        "_id": "6571eb70320499f416931fe8",
        "id": 18,
        "date": "2019-04-10T00:00:00.000Z",
        "description": "badday",
        "category": "school",
        "buyer": "giacomo",
        "cost": 329,
        "users": [
            {
                "username": "luca",
                "amount": 109.66666666666667
            },
            {
                "username": "luca",
                "amount": 109.66666666666667
            },
            {
                "username": "giacomo",
                "amount": 109.66666666666667
            }
        ]
    },
    {
        "_id": "6571eb70320499f416931feb",
        "id": 21,
        "date": "2021-11-14T00:00:00.000Z",
        "description": "boringexperience",
        "category": "school",
        "buyer": "giacomo",
        "cost": 299,
        "users": [
            {
                "username": "giorgia",
                "amount": 99.66666666666667
            },
            {
                "username": "giacomo",
                "amount": 99.66666666666667
            },
            {
                "username": "giacomo",
                "amount": 99.66666666666667
            }
        ]
    },
    {
        "_id": "6571eb70320499f416931fff",
        "id": 41,
        "date": "2020-04-09T00:00:00.000Z",
        "description": "badtool",
        "category": "sport",
        "buyer": "giacomo",
        "cost": 349,
        "users": [
            {
                "username": "franco",
                "amount": 116.33333333333333
            },
            {
                "username": "franco",
                "amount": 116.33333333333333
            },
            {
                "username": "giacomo",
                "amount": 116.33333333333333
            }
        ]
    }
]

期望：当提交year=2021, month=11时，仅返回第三个文档。

报错核心原因：数据库中date字段存储的是字符串类型，但MongoDB的$year、$month操作符仅支持Date类型数据。提供两种解决思路：

思路1：查询时动态转换字符串为Date #

修改后端查询逻辑，用$dateFromString将字符串日期转为Date类型后再提取年月，同时修复异步操作未加await的问题：

app.get("/api/budget/:year/:month", verify, async (req, res) => {
  const user = req.session.user;

  const year = Number(req.params.year);
  const month = Number(req.params.month);

  console.log("Got " + year + "/" + month);

  try {
    const expenses = await db
      .collection("expenses")
      .find({
        "users.username": user.username,
        $expr: {
          $and: [
            { $eq: [{ $year: { $dateFromString: { dateString: "$date" } } }, year] },
            { $eq: [{ $month: { $dateFromString: { dateString: "$date" } } }, month] },
          ],
        },
      })
      .toArray();

    console.log(expenses);
    res.json(expenses);
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: "Server error" });
  }
});

思路2：批量修改数据库字段类型（推荐） #

如果允许修改数据库，将所有date字段从字符串转为Date类型，后续查询更高效：

// 在MongoDB Shell或MongoDB Compass中执行此命令
db.expenses.updateMany(
  { date: { $type: "string" } },
  [
    { $set: { date: { $dateFromString: { dateString: "$date" } } } }
  ]
)

转换完成后，原后端查询逻辑即可正常工作。

前端代码优化（可选） #

补充变量声明避免全局污染，同时优化异步代码写法：

async filter(){
    const month = this.$refs.month.value;
    const year = this.$refs.year.value;

    if(month != "Month" && year != ""){
      const addressToFetch = "/api/budget/" + year + "/" + month;
      console.log("Fetching" + addressToFetch);
      try {
        const response = await fetch(addressToFetch);
        const data = await response.json();
        this.expenses = data;
        console.log(data);
      } catch (err) {
        console.error("请求失败：", err);
      }
    }
}

内容的提问来源于stack exchange，提问作者Eminent Emperor Penguin