MongoDB嵌套数组事务更新求助：多层过滤与汇总字段同步

需求说明 #

需要根据指定的user_id、walletId和transId，更新对应交易记录的金额，同时同步更新：

• 交易所属分类（Credit/Debit）的totalCredit/totalDebit
• 顶层文档的creditTotal/debitTotal
• 钱包的sumTotal和顶层的creditDebitTotal

现有MongoDB数据 #

[
  {
    "_id": {"$oid": "6459f1020162e7e3649729c9"},
    "user_id": {"$oid": "644fd02b7add0a888e2e8e84"},
    "creditTotal": 500,
    "debitTotal": 0,
    "creditDebitTotal": 500,
    "userWallet": [
      {
        "userName": "hhjj",
        "mobileNumber": "6666699888",
        "sumTotal": 500,
        "debit": {"totalDebit": 0,"transactions": []},
        "credit": {
          "totalCredit": 500,
          "transactions": [
            {"amount": 500,"date": "2023-05-17","description": "didi","_id": {"$oid": "64644941a6a3a7abdbf90316"}}
          ]
        },
        "_id": {"$oid": "6462f70a2ff97a1eac2290a8"}
      }
    ]
  },
  {
    "_id": {"$oid": "6459f1020162e7e3649729ce"},
    "user_id": {"$oid": "6459ecf2dcb04e1d845bb6f2"},
    "creditTotal": 2500,
    "debitTotal": 1000,
    "creditDebitTotal": 1500,
    "userWallet": [
      {
        "userName": "h",
        "mobileNumber": "22222222222",
        "sumTotal": 1500,
        "debit": {
          "totalDebit": 1000,
          "transactions": [
            {"amount": 1000,"date": "2023-05-16","description": "rt","_id": {"$oid": "64635ad2a6a3a7abdbf8f4b1"}}
          ]
        },
        "credit": {
          "totalCredit": 2500,
          "transactions": [
            {"amount": 500,"date": "2023-05-16","description": "ft","_id": {"$oid": "646359e3a6a3a7abdbf8f4ac"}},
            {"amount": 2000,"date": "2023-05-16","description": "rt","_id": {"$oid": "64635ba6a6a3a7abdbf8f4c7"}}
          ]
        },
        "_id": {"$oid": "64630eb92ff97a1eac229517"}
      }
    ]
  }
]

输入参数 #

{
  "user_id": "6459ecf2dcb04e1d845bb6f2", // 一级匹配的user_id
  "walletId": "64630eb92ff97a1eac229517", // 匹配userWallet对象
  "transId": "646359e3a6a3a7abdbf8f4ac", // 匹配transaction数组对象
  "amount": "5000", // 更新后的金额
  "type": "Credit" // 检查userWallet的credit或debit分类
}

尝试的Mongoose代码（无法实现二级过滤） #

const filter = {
  "user_id": userId,
  "userWallet._id": id,
  "userWallet.credit.transactions._id": transId
};
const update = {
  $set: {
    "userWallet.$.credit.transactions.$[transaction].amount": amount,
  }
};
const arrayFilters = [{"transaction._id": transId}];
const updateOptions = {returnOriginal: false};
var updatedDocument = await mongoose.connection.collections.wallets.collection.findOneAndUpdate(filter, update, { arrayFilters, ...updateOptions });

正确解决方案 #

要实现多层数组的精准匹配+汇总字段同步更新，需要分两步：先获取原交易金额计算差值，再执行包含多字段更新的原子操作。

步骤1：查询原交易金额，计算差值 #

首先找到目标交易的原金额，计算更新前后的差值，用于后续更新汇总字段：

const ObjectId = require('mongoose').Types.ObjectId;
const { user_id, walletId, transId, amount: newAmount, type } = inputParams;

// 转换为MongoDB的ObjectId类型
const userIdObj = new ObjectId(user_id);
const walletIdObj = new ObjectId(walletId);
const transIdObj = new ObjectId(transId);
const newAmountNum = Number(newAmount);

// 查询原交易记录及关联汇总值
const originalDoc = await mongoose.connection.collections.wallets.findOne({
  "user_id": userIdObj,
  "userWallet._id": walletIdObj
}, {
  projection: {
    [`userWallet.$.${type.toLowerCase()}.transactions`]: {
      $elemMatch: { "_id": transIdObj }
    },
    [`userWallet.$.${type.toLowerCase()}.total${type}`]: 1,
    [`${type.toLowerCase()}Total`]: 1,
    "userWallet.$.sumTotal": 1,
    "creditDebitTotal": 1
  }
});

if (!originalDoc) throw new Error("目标文档未找到");
const originalTransaction = originalDoc.userWallet[0][type.toLowerCase()].transactions[0];
const originalAmount = originalTransaction.amount;
const diff = newAmountNum - originalAmount; // 金额变动差值

步骤2：执行原子更新操作 #

构造包含多层数组匹配的过滤条件、更新字段和数组过滤器，同步更新所有关联字段：

const filter = {
  "user_id": userIdObj,
  "userWallet._id": walletIdObj,
  [`userWallet.${type.toLowerCase()}.transactions._id`]: transIdObj
};

const updateFields = {
  // 更新目标交易的金额
  $set: {
    [`userWallet.$[wallet].${type.toLowerCase()}.transactions.$[trans].amount`]: newAmountNum
  },
  // 同步更新所有汇总字段
  $inc: {
    [`userWallet.$[wallet].${type.toLowerCase()}.total${type}`]: diff,
    [`${type.toLowerCase()}Total`]: diff,
    "userWallet.$[wallet].sumTotal": diff,
    "creditDebitTotal": diff
  }
};

const arrayFilters = [
  {"wallet._id": walletIdObj}, // 精准匹配目标钱包
  {"trans._id": transIdObj}     // 精准匹配目标交易
];

const updateOptions = {
  returnOriginal: false,
  new: true // 返回更新后的完整文档
};

const updatedDocument = await mongoose.connection.collections.wallets.findOneAndUpdate(
  filter,
  updateFields,
  { arrayFilters, ...updateOptions }
);

console.log(updatedDocument);

代码说明 #

1. 类型转换：将输入的字符串ID转为MongoDB的ObjectId，确保匹配精准。
2. 差值计算：通过查询原交易金额得到变动差值，用$inc原子操作更新汇总字段，避免并发数据不一致问题。
3. 多层数组匹配：使用两个数组过滤器分别定位userWallet和transactions数组中的目标元素，实现精准更新。
4. 原子性保障：通过一次findOneAndUpdate操作完成所有字段更新，保证数据修改的原子性。

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