Reverse $unwind into $lookup array

Working with Data
1

Hello everyone, I’ve 3 collections and i want to show all of them and for that i use lookup.

And after that i want to use divide method on one of the collection to divide some fields by some value(i.e. 5) but it returns an error that $divide can’t be used on array, so i used unwind and apply the divide method and it was working perfectly but the issue is that i want to merge them back in one array. Followed are the collections and query and required output.

---------- c_accounts --------------
//   01 
{ 
    "accountId" : "12345", 
    "customerId" : "1234", 
    "accountNumber" : "AC12345", 
    "balance" : 3242.2, 
    "balanceAed" : 32423.23
}, 
//   02
{ 
    "accountId" : "12346", 
    "customerId" : "1234", 
    "accountNumber" : "AC12346", 
    "balance" : 12131, 
    "balanceAed" : 123.1
}
---------c_transactions----------
//   01 
{
     "customerId" : "1234", 
     "accountId" : "12345", 
     "transactionId" : "T12345", 
     "referenceNumber" : "R12345"
}, 
//   02
{
     "customerId" : "1234", 
     "accountId" : "12346", 
     "transactionId" : "T12346", 
     "referenceNumber" : "R12346"
}
-------------c_cards---------------
//    01
{
        "customerId" : "1234", 
        "accountId" : "12345", 
        "cardId" : "C1234", 
        "cardHolderName" : "John Doe",
        "LimitAmount" : 15000.5, 
        "PaymentAmount" : 5000.5

},
//   02
{
        "customerId" : "1234", 
        "accountId" : "12346", 
        "cardId" : "C1236", 
        "cardHolderName" : "John Doe",
        "LimitAmount" : 15000.5, 
        "PaymentAmount" : 5000.5

}

----------------------- Query -------------------

    db.getCollection("accounts").aggregate(
    [
        { 
            "$match" : { 
                "customerId" : "1234"
            }
        }, 
        { 
            "$lookup" : { 
                "from" : "transactions", 
                "localField" : "accountId", 
                "foreignField" : "accountId", 
                "as" : "Transactions"
            }
        }, 
        { 
            "$lookup" : { 
                "from" : "cards", 
                "localField" : "accountId", 
                "foreignField" : "accountId", 
                "as" : "Cards"
            }
        }, 
        { 
            "$unwind" : { 
                "path" : "$Cards"
            }
        }, 
        { 
            "$project" : { 
                "_id" : 0.0, 
                "Transactions._id" : 0.0, 
                "Cards._id" : 0.0
            }
        }, 
        { 
            "$project" : { 
    					// C-Accounts
    							"accountId" : 1.0, 
    							"customerId" : 1.0, 
    							"accountNumber" : 1.0, 
    							"balance" : 1.0, 
    							"balanceAed" : 1.0, 
    					// C-Transactions
    							"Transactions.accountId" : 1.0, 
    							"Transactions.customerId" : 1.0, 
    							"Transactions.transactionId" : 1.0, 
    							"Transactions.referenceNumber" : 1.0, 
    					// C-Cards
    							"Cards.customerId" : 1.0, 
    							"Cards.accountId" : 1.0, 
    							"Cards.cardId" : 1.0, 
    							"Cards.cardHolderName" : 1.0, 

    							"Cards.LimitAmount" : { 
    								"$divide" : [
    									"$Cards.LimitAmount", 
    									5.0
    								]
    							}, 
    							"Cards.PaymentAmount" : { 
    								"$divide" : [
    									"$Cards.PaymentAmount", 
    									5.0
    								]
    							}
    					}
        }
    ], 
    { 
        "allowDiskUse" : false
    }
    );

-------- Expected Output ----------

[
   {
    "accountId" : "12345", 
    "customerId" : "1234", 
    "accountNumber" : "AC12345", 
    "balance" : 3242.2, 
    "balanceAed" : 32423.23
   },{
    "accountId" : "12346", 
    "customerId" : "1234", 
    "accountNumber" : "AC12346", 
    "balance" : 12131, 
    "balanceAed" : 123.1
 },
Transactions:[
   {
     "customerId" : "1234", 
     "accountId" : "12345", 
     "transactionId" : "T12345", 
     "referenceNumber" : "R12345"
}, 
{
     "customerId" : "1234", 
     "accountId" : "12346", 
     "transactionId" : "T12346", 
     "referenceNumber" : "R12346"
   }
],
Cards: [{
        "customerId" : "1234", 
        "accountId" : "12345", 
        "cardId" : "C1234", 
        "cardHolderName" : "John Doe",
        "LimitAmount" : 5000.5, 
        "PaymentAmount" : 1000.5

},
  {
        "customerId" : "1234", 
        "accountId" : "12346", 
        "cardId" : "C1236", 
        "cardHolderName" : "John Doe",
        "LimitAmount" : 3000.5, 
        "PaymentAmount" : 1000.5
  }
]
]
2

I am not to sure if I understand your requirements but I would take a look at the $push in https://docs.mongodb.com/manual/reference/operator/update/push/#up._S_push

3

Have you considered using the $map operator in the $project stage instead of $unwind?

$map will apply an operation to each element in the array:

Using $map will remove the need to $unwind with a subsequent $group.

3 Likes
4

$push with $group, but the issue is with the main collection. here is the group clause:
{
_id: “customerId”,
“Accounts” : {$push: “$accounts”},
“Transactions” : {$push: “$Transactions”},
“Cards” : {$push: “$Cards”}
}

  • it show empty array of accounts and one more thing that i duplicates the records.
5

Don’t use $unwind or $group - just use $divide inside of $map, like Justin suggested.

1 Like
6

Then it show null value in the field.

7

Hi @Nabeel_Raza,

Thanks for providing the example input, the expected output and the aggregation pipeline that you’ve tried.

As mentioned by Justin and Asya, you can utilise $map instead of $unwind here. So replacing all of the pipeline stages after $unwind from your example, below is an example on how you could utilise $divide inside of $map :

db.accounts.aggregate([
        {"$match" : { "customerId" : "1234" } }, 
        { 
            "$lookup" : { 
                "from" : "transactions", 
                "localField" : "accountId", 
                "foreignField" : "accountId", 
                "as" : "Transactions"
            }
        }, 
        { 
            "$lookup" : { 
                "from" : "cards", 
                "localField" : "accountId", 
                "foreignField" : "accountId", 
                "as" : "Cards"
            }
        }, 
        {"$addFields": {
                "Cards": {
                    "$map":{
                        "input":"$Cards", 
                        "in": {
                         "customerId": "$$this.customerId",
                         "accountId": "$$this.accountId",
                         "cardId": "$$this.cardId",
                         "cardHolderName": "$$this.cardHolderName",
                         "LimitAmount": { "$divide": [ "$$this.LimitAmount", 5.0 ] }, 
                         "PaymentAmount": { "$divide": [ "$$this.PaymentAmount", 5.0 ] }, 
                        }
                    }
                },     
            }
        }, 
        {"$project": {
            "_id":0, 
            "Transactions._id":0
            }
        }
])

This should have solved your issue with using $unwind and having to merge them back in. See also $addFields for more information.

However looking at your desired output, it looks like there is another issue. It seems that you’re trying to group all documents in accounts, transactions and cards collections matching customerId to a single document. If this is the case, then you need to group before the $lookup, for example:

db.accounts.aggregate([
        {"$match" : { "customerId" : "1234"} }, 
        {
            "$group": {
                "_id": null, 
                "Accounts":{"$push":"$$ROOT"}
            }
        }, 
        { 
            "$lookup" : { 
                "from" : "transactions", 
                "localField" : "Accounts.accountId", 
                "foreignField" : "accountId", 
                "as" : "Transactions"
            }
        }, 
        { 
            "$lookup" : { 
                "from" : "cards", 
                "localField" : "Accounts.accountId", 
                "foreignField" : "accountId", 
                "as" : "Cards"
            }
        }, 
        {"$addFields": {
                "Cards": {
                    "$map":{
                        "input":"$Cards", 
                        "in": {
                        "customerId": "$$this.customerId",
                        "accountId": "$$this.accountId",
                        "cardId": "$$this.cardId",
                        "cardHolderName": "$$this.cardHolderName",
                        "LimitAmount": { "$divide": [ "$$this.LimitAmount", 5.0 ] }, 
                        "PaymentAmount": { "$divide": [ "$$this.PaymentAmount", 5.0 ] }, 
                        }
                    }
                },     
            }
        }, 
        {"$project" : { 
            "_id" : 0, 
            "Accounts._id": 0,
            "Transactions._id" : 0, 
            "Cards._id" : 0
            }
        }
    ])

Having said all the above, after looking at the collection schemas and the desired output of your aggregation pipeline, if this is a frequently used query I’d recommend to reconsider the collection schemas. Please review Schema Design: Summary for examples of different patterns.

Regards,
Wan.

3 Likes
8 
{
            "customerId" : "1234",
            "accountId" : "12345",
            "cardId" : "C1238",
            "cardHolderName" : "John Moe",
            "LimitAmount" : null,
            "PaymentAmount" : null
        }

@wan it through null values. how can we avoid that.

9

Here is the solution of the above problem.

db.getCollection("accounts").aggregate(
    [
        { 
            "$match" : { 
                "customerId" : "1234"
            }
        }, 
        { 
            "$lookup" : { 
                "from" : "transactions", 
                "localField" : "accountId", 
                "foreignField" : "accountId", 
                "as" : "Transactions"
            }
        }, 
        { 
            "$lookup" : { 
                "from" : "cards", 
                "localField" : "accountId", 
                "foreignField" : "accountId", 
                "as" : "Cards"
            }
        }
        , 
        { 
            "$unwind" : { 
                "path" : "$Cards"
            }
        }, 
        { 
            "$project" : { 
                "accountId" : 1.0, 
                "customerId" : 1.0, 
                "accountNumber" : 1.0, 
                "balance" : 1.0, 
                "balanceAed" : 1.0, 

                "Transactions.accountId" : 1.0, 
                "Transactions.customerId" : 1.0, 
                "Transactions.transactionId" : 1.0, 
                "Transactions.referenceNumber" : 1.0, 

                "Cards.customerId" : 1.0, 
                "Cards.accountId" : 1.0, 
                "Cards.cardId" : 1.0, 
                "Cards.cardHolderName" : 1.0, 
                "Cards.LimitAmount" : { 
                    "$divide" : [
                        "$Cards.LimitAmount", 
                        5.0
                    ]
                }, 
                "Cards.PaymentAmount" : { 
                    "$divide" : [
                        "$Cards.PaymentAmount", 
                        5.0
                    ]
                }, 
            }
        },
        {
       $group:
         {
           _id: {
                "accountId" : "$accountId", 
                "customerId" : "$customerId", 
                "accountNumber" : "$accountNumber", 
                "balance" : "$balance", 
                "balanceAed" : "$balanceAed", 
            },
           Transactions: { $addToSet: "$Transactions" }
           ,Cards: { $addToSet: "$Cards" }
         }
        },
        { 
            "$project" : { 
                "_id" : 0, 
                "Accounts":"$_id",
                "Transactions" : "$Transactions", 
                "Cards" : "$Cards"
            }
        }
     ], 
    { 
        "allowDiskUse" : true
    }
);
1 Like
10

@Nabeel_Raza, glad that you’ve found a solution.

I noticed that the output of the aggregation pipeline is different to the expected output from the original post. Given your collection/document examples from the original post, an example pipeline to get similar output to the pipeline above is below:

db.accounts.aggregate( [
        { "$match" : {  "customerId" : "1234"} }, 
        { 
            "$lookup" : { 
                "from" : "transactions", 
                "localField" : "accountId", 
                "foreignField" : "accountId", 
                "as" : "Transactions"
            }
        }, 
        { 
            "$lookup" : { 
                "from" : "cards", 
                "localField" : "accountId", 
                "foreignField" : "accountId", 
                "as" : "Cards"
            }
        }, 
        {"$addFields": {
                "Accounts.accountId": "$accountId",
                "Accounts.customerId": "$customerId", 
                "Accounts.accountNumber": "$accountNumber", 
                "Accounts.balance":"$balance", 
                "Accounts.balanceAed":"$balanceAed",   
                "Cards": {
                    "$map":{
                        "input":"$Cards", 
                        "in": {
                         "customerId": "$$this.customerId",
                         "accountId": "$$this.accountId",
                         "cardId": "$$this.cardId",
                         "cardHolderName": "$$this.cardHolderName",
                         "LimitAmount": { "$divide": [ "$$this.LimitAmount", 5.0 ] }, 
                         "PaymentAmount": { "$divide": [ "$$this.PaymentAmount", 5.0 ] }, 
                        }
                    }
                },  
            }
        }, 
        { 
            "$project" : { 
                "_id" : 0, 
                "Transactions._id" : 0, 
                "Cards._id" : 0,
                "accountId":0, 
                "customerId":0, 
                "accountNumber":0, 
                "balance":0, 
                "balanceAed":0
            }
        }, 
])

This should avoid using $unwind and grouping by Accounts collection. Also, avoiding a result of an array of arrays for Transactions field.

Worth mentioning that if you’re dealing with monetary data i.e. LimitAmount and PaymentAmount and you need to emulate decimal rounding with exact precision I would recommend to look into Decimal BSON type. See also Model Monetary Data for more information.

Still related to data modelling, I would suggest to review Schema Design: Summary for example of different patterns.

Regards,
Wan.

1 Like
11

Thanks @wan. Your query is still showing null vlaues. image