MongoDB+Node.js中关联字段空字符串处理方案咨询

问题背景 #

使用Node.js + Mongoose@8.2.2操作MongoDB，各模型记录量超20万。因操作失误，部分关联字段（如TransactionSchema中的rpc_id）被存储为空字符串，且无法统计此类数据量。查询文档时触发CastError：空字符串无法转换为ObjectId，但业务要求必须展示所有文档，不能通过{rpc_id: {$ne: ''}}过滤，需将所有空字符串的关联字段修改为null。

错误日志 #

Something went wrong in paginateData method with error: CastError: Cast to ObjectId failed for value "" (type string) at path "_id" for model "rpc_credentials" error
[Caller: postMerchantTransactionList] : [24/4/2024, 9:40:22 am 93] : error :  CastError: Cast to ObjectId failed for value "" (type string) at path "_id" for model "rpc_credentials"
    at SchemaObjectId.cast (/var/www/html/coinremmiter-admin/node_modules/mongoose/lib/schema/objectId.js:250:11)
    at SchemaType.applySetters (/var/www/html/coinremmiter-admin/node_modules/mongoose/lib/schemaType.js:1221:12)
    at SchemaType.castForQuery (/var/www/html/coinremmiter-admin/node_modules/mongoose/lib/schemaType.js:1636:17)
    at /var/www/html/coinremmiter-admin/node_modules/mongoose/lib/schemaType.js:1597:18
    at Array.map (<anonymous>)
    at SchemaObjectId.handle$in (/var/www/html/coinremmiter-admin/node_modules/mongoose/lib/schemaType.js:1593:14)
    at SchemaType.castForQuery (/var/www/html/coinremmiter-admin/node_modules/mongoose/lib/schemaType.js:1632:20)
    at cast (/var/www/html/coinremmiter-admin/node_modules/mongoose/lib/cast.js:352:39)
    at Query.cast (/var/www/html/coinremmiter-admin/node_modules/mongoose/lib/query.js:4778:12)
    at Query._castConditions (/var/www/html/coinremmiter-admin/node_modules/mongoose/lib/query.js:2199:10) {
  stringValue: '""',
  messageFormat: undefined,
  kind: 'ObjectId',
  value: '',
  path: '_id',
  reason: BSONError: input must be a 24 character hex string, 12 byte Uint8Array, or an integer
      at new ObjectId (/var/www/html/coinremmiter-admin/node_modules/bson/lib/bson.cjs:2288:23)
      at castObjectId (/var/www/html/coinremmiter-admin/node_modules/mongoose/lib/cast/objectid.js:25:12)
      at SchemaObjectId.cast (/var/www/html/coinremmiter-admin/node_modules/mongoose/lib/schema/objectId.js:248:12)
      at SchemaType.applySetters (/var/www/html/coinremmiter-admin/node_modules/mongoose/lib/schemaType.js:1221:12)
      at SchemaType.castForQuery (/var/www/html/coinremmiter-admin/node_modules/mongoose/lib/schemaType.js:1636:17)
      at /var/www/html/coinremmiter-admin/node_modules/mongoose/lib/schemaType.js:1597:18
      at Array.map (<anonymous>)
      at SchemaObjectId.handle$in (/var/www/html/coinremmiter-admin/node_modules/mongoose/lib/schemaType.js:1593:14)
      at SchemaType.castForQuery (/var/www/html/coinremmiter-admin/node_modules/mongoose/lib/schemaType.js:1632:20)
      at cast (/var/www/html/coinremmiter-admin/node_modules/mongoose/lib/cast.js:352:39),
  valueType: 'string',
  model: Model { rpc_credentials }
}

相关Schema代码 #

TransactionSchema #

const TransactionSchema = new Schema({
    wallet_id: { type: String },
    coin_id: { type: String },
    rpc_id: { type: String },
    __v: { type: Number, select: false }
}, { timestamps: true, strict: false });

TransactionSchema.virtual(
    "rpc", // 虚拟字段名称，用于 populate
    {
        ref: "rpc_credentials",
        localField: "rpc_id", // Transaction模型中的关联字段
        foreignField: "_id", // rpc_credentials模型中的关联字段
    }
)

rpc_credentials Schema #

const rpcCredencialSchema = new Schema({
    rpcConnected: { type: Boolean, default: false },
    __v: { type: Number, select: false }
}, { timestamps: true, strict: false });

解决方案 #

1. 批量更新数据库中的空字符串字段 #

直接通过MongoDB批量更新操作，将所有rpc_id为空字符串的文档替换为null。执行前务必备份数据：

const Transaction = mongoose.model('Transaction', TransactionSchema);

async function fixEmptyRpcId() {
  try {
    const updateResult = await Transaction.updateMany(
      { rpc_id: '' }, // 匹配所有rpc_id为空字符串的文档
      { $set: { rpc_id: null } } // 将空字符串替换为null
    );
    console.log(`更新完成，共修改${updateResult.modifiedCount}条文档`);
  } catch (error) {
    console.error('更新失败:', error);
  }
}

// 执行更新
fixEmptyRpcId();

Mongoose处理关联时会忽略null值，不会触发ObjectId转换错误，同时不影响业务展示需求。

2. 优化Schema从源头避免问题 #

修改TransactionSchema，将rpc_id指定为ObjectId类型并允许null，后续写入时自动拦截不符合格式的数据：

const TransactionSchema = new Schema({
    wallet_id: { type: String },
    coin_id: { type: String },
    rpc_id: { 
      type: mongoose.Schema.Types.ObjectId, 
      ref: 'rpc_credentials', 
      default: null // 默认值设为null
    },
    __v: { type: Number, select: false }
}, { timestamps: true, strict: false });

// 保留虚拟关联配置
TransactionSchema.virtual("rpc", {
    ref: "rpc_credentials",
    localField: "rpc_id",
    foreignField: "_id",
})

3. 临时应急方案（无需修改数据库） #

若暂时无法修改数据库，可在Schema中添加自定义转换逻辑，自动将空字符串转为null：

// 为rpc_id字段添加自定义转换规则
TransactionSchema.path('rpc_id').cast(function(value) {
  if (value === '') {
    return null;
  }
  return value;
});

此方法在Mongoose处理字段时自动转换，无需修改数据库，但长远来看，批量更新数据是更彻底的解决方案。

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