I have a document structure such as:
{
"id": "123",
"accountBalance": 100
"transactions": [
{"txnid": 1, "amount": 50, "currentBalance": 50},
{"txnid": 2, "amount": 50, "currentBalance": 100}
]
}
I wish to update a document by adding a transaction. I want to find the document where the id = 123 and, if the transaction is a withdrawal, the balance is sufficient. I want to update it so that accountBalance reflects the new amount and a new entry is $push into the transactions array.
I am using something like this for a hypothetical withdrawal of 50 cents:
db.collection.update(
{
"$and": [{"id": 123, "accountBalance": {"$gte", 100}]
},
{
"$push": {
"transactions": {
"transactionId": "4e920b00-f46f-431f-ba06-9c65bbf1837c",
"transactionType": "STAKE",
"transactionAmount": -100,
"requestingClient": "test person",
// whoops- this won't work:
"balance": {
"$add": [
"$currentBalance",
-100
]
}
}
},
"$inc": {
"accountBalance": -100
}
},
{...}
)
The accountBalance and the currentBalance of the last transaction entry must always be equal.
The problem comes when I try to push a new array element into transactions. I need to set the currentBalance field with respect to the current accountBalance. The add syntax I have here merely adds an object with the key “$add” and the two elements of the operation.
Is it possible to push an array using the result of an $add operation in a single modify statement? As this is a new design I can simply get rid of currentBalance and rely on the accountBalance field but for readability purposes that is less desirable.
You can use an update with aggregation pipeline to achieve what you want. I suggest you to always reference to the accountBalance field for consistency in calculation.
db.collection.update({
"id": "123",
"accountBalance": {
$gte: 100
}
},
[
{
"$set": {
"accountBalance": {
"$subtract": [
"$accountBalance",
100
]
},
transactions: {
"$concatArrays": [
"$transactions",
[
// your new transaction here
{
"transactionId": "4e920b00-f46f-431f-ba06-9c65bbf1837c",
"transactionType": "STAKE",
"transactionAmount": -100,
"requestingClient": "test person",
"currentBalance": {
"$subtract": [
"$accountBalance",
100
]
}
}
]
]
}
}
}
])
Mongo Playground