Mongoose中Product与Category多对多关联查询问题求助

我定义了Product和Category两个Mongoose模型，二者为多对多关系（Product的category字段是Category的ObjectId数组，Category的products字段是Product的ObjectId数组），但使用aggregate的$lookup查询时返回空对象，求正确的关联方式。

Product模型代码 #

//Product.js
const mongoose = require("mongoose");
const { Schema } = mongoose;
const { ObjectId } = Schema;
const product = {
  name: {
    type: String,
    required: true,
    trim: true,
    minlength: [2, "Too short"],
    maxlength: [32, "Too long"],
    unique: true,
  },
  slug: {
    type: String,
    unique: true,
    lowercase: true,
    index: true,
  },
  category: [
    {
      type: ObjectId,
      ref: "Category",
    },
  ],
  description: {
    type: String,
    maxlength: 200,
  },
  price: {
    type: Number,
    required: true,
    trim: true,
    maxlength: 32,
  },
  shipping: {
    type: String,
    enum: ["Yes", "No"],
  },
  color: [
    {
      type: String,
      enum: ["Black", "Brown", "Silver", "White", "Blue"],
    },
  ],
  sold: {
    type: Number,
    default: 0,
  },
  quantity: {
    type: Number,
  },
  images: [
    {
      public_id: String,
      url: String,
    },
  ],
};
const productSchema = new Schema(product, { timestamps: true });
const Product = mongoose.model("Product", productSchema);
module.exports = Product;

Category模型代码 #

//Category.js
const mongoose = require("mongoose");
const { Schema } = mongoose;
const { ObjectId } = Schema;
const category = {
  name: {
    type: String,
    required: true,
    trim: true,
    max: 32,
    unique: true,
  },
  subCategories: [
    {
      type: Schema.Types.ObjectId,
      ref: "Category",
    },
  ],
  parent: {
    type: Schema.Types.ObjectId,
    ref: "Category",
  },
  products: [
    {
      type: ObjectId,
      ref: "Product",
    },
  ],
  slug: {
    type: String,
    required: "URL can't be empty",
    unique: true,
  },
};
const categorySchema = new Schema(category, { timestamps: true });

const autoPopulateChildren = function (next) {
  this.populate("subCategories");
  next();
};
categorySchema
  .pre("findOne", autoPopulateChildren)
  .pre("findById", autoPopulateChildren)
  .pre("find", autoPopulateChildren);

const Category = mongoose.model("Category", categorySchema);
module.exports = Category;

尝试的查询代码 #

router.get("/products", [RequireSignIn], async (req, res) => {
  try {
    const products = await Product.find({}).aggregate({
      $lookup: {
        from: "Category",
        localField: "category",
        foreignField: "_id",
        as: "ProductCategories",
      },
    });
    return res.status(200).send(products); 

  } catch (error) {
    return res.status(200).send(error);
  }
});

问题原因及解决方案 #

你的查询失败主要有几个核心问题：

1. find() 和 aggregate() 不能链式调用：Mongoose中find()返回的是Query对象，不能直接跟aggregate()，需要单独调用Product.aggregate()。
2. $lookup的from参数错误：Mongoose默认会将模型名转为小写复数的集合名，所以Category模型对应的集合是categories，不是Category。
3. 错误状态码使用不当：捕获到错误时应该返回500而非200，否则前端无法正确识别异常。

方案一：使用Mongoose原生populate（推荐） #

既然你已经在模型中定义了ref字段，直接用populate就能轻松实现关联查询，比aggregate更简洁：

router.get("/products", [RequireSignIn], async (req, res) => {
  try {
    // populate会自动把category数组里的ObjectId替换成对应的Category文档
    const products = await Product.find({}).populate("category");
    return res.status(200).send(products);
  } catch (error) {
    return res.status(500).send({ message: "查询失败", error });
  }
});

方案二：修正aggregate写法 #

如果一定要用aggregate，调整代码如下：

router.get("/products", [RequireSignIn], async (req, res) => {
  try {
    const products = await Product.aggregate([
      {
        $lookup: {
          from: "categories", // 正确的集合名：小写复数
          localField: "category",
          foreignField: "_id",
          as: "ProductCategories",
        },
      },
    ]);
    return res.status(200).send(products);
  } catch (error) {
    return res.status(500).send({ message: "查询失败", error });
  }
});

如果需要对关联的分类进行额外过滤（比如同时加载子分类），可以在$lookup中加入pipeline：

router.get("/products", [RequireSignIn], async (req, res) => {
  try {
    const products = await Product.aggregate([
      {
        $lookup: {
          from: "categories",
          let: { categoryIds: "$category" },
          pipeline: [
            // 匹配当前产品关联的分类
            { $match: { $expr: { $in: ["$_id", "$$categoryIds"] } } },
            // 同时加载分类的子分类
            { $lookup: { from: "categories", localField: "subCategories", foreignField: "_id", as: "subCategories" } }
          ],
          as: "ProductCategories",
        },
      },
    ]);
    return res.status(200).send(products);
  } catch (error) {
    return res.status(500).send({ message: "查询失败", error });
  }
});

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