I have below kind of schema
Problem - I want to get all succeeded transaction in list with their respective user with some extra field like reward - for that particular transaction. like if paid amount in 10 then reward will be 0.3 times -> 3. But i need 0.3 in case of 1st successful payment only for others it will be 0.1.
tried: I have achieved partial output, not able to get reward calculation based on first transaction
The output will be -
[
{
_id: 1,
name: 'Stephen',
transactions: [
{
_id: 1,
paidAmount: 10,
reward: 3
},
{
_id: 3,
paidAmount: 20,
reward: 2
}
]
},
{
_id: 2,
user: 'Peter',
transactions: [
{
_id: 2,
paidAmount: 5,
reward: 0.15
}]
}
]
Hi i have found the solution for above
db.users.aggregate([
{
$match: {
_id: 1,
},
},
{
$lookup: {
from: "payments",
localField: "_id",
foreignField: "user",
as: "payments",
},
},
{
$addFields: {
transactions: {
$reduce: {
input: "$payments",
initialValue: [],
in: {
$concatArrays: [
"$$value",
{
$filter: {
input: "$$this.transactions",
cond: {
$eq: [
"$$this.status",
"succeeded"
]
},
},
},
],
},
},
},
},
},
{
$project: {
transactions: {
$map: {
input: "$transactions",
as: "t",
in: {
paidAmount: "$$t.paidAmount",
status: "$$t.status",
createdAt: "$$t.createdAt",
reward: {
$cond: {
if: {
$eq: [
{
$indexOfArray: [
"$transactions",
"$$t"
]
},
0
]
},
then: {
$multiply: [
"$$t.paidAmount",
0.3
]
},
else: {
$multiply: [
"$$t.paidAmount",
0.1
]
},
},
},
},
},
},
},
},
{
$project: {
id: {
$toString: "$_id"
},
totalAMount: {
$sum: "$transactions.paidAmount"
},
totalReward: {
$sum: "$transactions.reward"
},
transactions: "$transactions",
},
},
])
Please comment if you find out a better solution/ approach.