Facing issues while using $addToSet clause(of group)

[Nabeel_Raza (Nabeel Raza Naqeebi)]

Hello Everyone, Let me share everything in detail.
I have 3 collections and i use lookup for joining them.
Then i first unwind the 2nd and 3rd collection to perform some mathematical operations(division) in projection.
It give multiple documents but i need one collection for that i use group clause and then i use projection. It gave me a desired output
But now i want to get the output in sorted order for 2nd collection (for which i use $addToSet clause) but it doesn’t give the desired output as the order is undefined.So i want to know the solution of this problem… I am sharing the sample collection and query which is as followed
Sample Collections

---------- a_collection  --------------
    //   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
    }
    ---------b_collection ----------
    //   01 
    {
         "customerId" : "1234", 
         "accountId" : "12345", 
         "transactionId" : "T12345", 
         "transactionDate" : ISODate("2018-02-13T16:53:33.324Z"),
         "referenceNumber" : "R12345"
    }, 
    //   02
    {
         "customerId" : "1234", 
         "accountId" : "12346", 
         "transactionId" : "T12346",  
         "transactionDate" : ISODate("2018-02-15T16:53:33.324Z"),
         "referenceNumber" : "R12346"
    }//   03
    {
         "customerId" : "1234", 
         "accountId" : "12346", 
         "transactionId" : "T12347",  
         "transactionDate" : ISODate("2018-01-13T16:53:33.324Z"),
         "referenceNumber" : "R12347"
    }
    -------------c_collection ---------------
    //    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("a_collection").aggregate(
    [
        {
            "$match" : {
                "customerId" : "1234"
            }
        },
        {
            "$lookup" : {
                "from" : "b_collection",
                "localField" : "customerId",
                "foreignField" : "customerId",
                "as" : "Transactions"
            }
        },
        {
            "$lookup" : {
                "from" : "c_collections",
                "localField" : "customerId",
                "foreignField" : "customerId",
                "as" : "Cards"
            }
        },
        {
            "$unwind" : {
                "path" : "$Transactions"
            }
        },
        {
            "$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, 
                "Transactions.transactionDate" : 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" }
         }
        },
        {
            "$sort" : {
                "Transactions.transactionDate" : 1.0
            }
        },
        { 
            "$project" : { 
                "_id" : 0, 
                "Accounts":"$_id",
                "Transactions" : "$Transactions", 
                "Cards" : "$Cards"
            }
        }
     ], 
    { 
        "allowDiskUse" : true
    }
);

Expected Output

---------- a_collection  --------------
    //   01 
    Accounts { 
        "accountId" : "12345", 
        "customerId" : "1234", 
        "accountNumber" : "AC12345", 
        "balance" : 3242.2, 
        "balanceAed" : 32423.23
    }
	Transaction{[
    {
         "customerId" : "1234", 
         "accountId" : "12345", 
         "transactionId" : "T12345", 
         "transactionDate" : ISODate("2018-01-13T16:53:33.324Z"),
         "referenceNumber" : "R12345"
    }, 
    //   02
    {
         "customerId" : "1234", 
         "accountId" : "12346", 
         "transactionId" : "T12346",  
         "transactionDate" : ISODate("2018-02-13T16:53:33.324Z"),
         "referenceNumber" : "R12346"
    }//   03
    {
         "customerId" : "1234", 
         "accountId" : "12346", 
         "transactionId" : "T12347",  
         "transactionDate" : ISODate("2018-02-15T16:53:33.324Z"),
         "referenceNumber" : "R12347"
    }
		]
	}
    Cards: [{
            "customerId" : "1234", 
            "accountId" : "12345", 
            "cardId" : "C1234", 
            "cardHolderName" : "John Doe",
            "LimitAmount" : 15000.5, 
            "PaymentAmount" : 5000.5
    },
    {
            "customerId" : "1234", 
            "accountId" : "12346", 
            "cardId" : "C1236", 
            "cardHolderName" : "John Doe",
            "LimitAmount" : 15000.5, 
            "PaymentAmount" : 5000.5
    }
	]

The 2nd collection is in sorted order w.r.t transactiondate field.
Kindly help me out.

[Prasad_Saya]

Nabeel,

You can try the following (that is move the $sort on Transactions.transactionDate immediately after the $unwind Transactions, and before the $group stage):

{
    "$unwind" : {
        "path" : "$Transactions"
    }
},
{
   "$sort" : {
                "Transactions.transactionDate" : 1
    }
},

[Nabeel_Raza (Nabeel Raza Naqeebi)]

yes i did, but the order was undefined.

[Prasad_Saya]

The Transactions array has the sub-documents sorted by the transactionDate - as I tried the altered code. That is what you are expecting.

[Nabeel_Raza (Nabeel Raza Naqeebi)]

Thanks @Prasad_Saya for your reply but $addToSet doesn’t project the output in any specified order(not ascending nor descending).

[Prasad_Saya]

The idea is that the pre-sorted order (data is sorted before it is grouped) is maintained. At least it can be seen as sorted with the posted sample data. You have to try with more data samples and see if it works for you.

[Nabeel_Raza (Nabeel Raza Naqeebi)]

Use $Push instead of $addToSet.
Sequence of the pipeline will be as followed
1 - apply $match on the customer field
2 - apply $lookup on other collections
3 - then $unwind the b and c collection to perform some operations.
4 - use $sort for sorting the collection array
5- use $project for projection and perform division operation with c_collection using $divide clause.
6 - As the output is in different document then use $group to merge them and use $push for b_collection.
7 - use final $project