Mongo DB Aggregate

Viewed 93

I have a collection with entries that look like:

{"placeId" : "0b980482-ab4b-4685-95ab-ecc2dcba1837","placeName" : "B8 Göteborg","created" : ISODate("2017-04-09T14:53:01.005Z"),"debitAccount" : "1581","debitAmount" : 26.0,"creditAccount" : "3013","creditAmount" : 23.2142791748047,"taxAccount" : "2620","taxAmount" : 2.78572010993958},
{"placeId" : "0b980482-ab4b-4685-95ab-ecc2dcba1837","placeName" : "B8 Göteborg","created" : ISODate("2017-04-09T14:53:01.005Z"),"debitAccount" : "1581","debitAmount" : 26.0,"creditAccount" : "3014","creditAmount" : 53.2142791748047,"taxAccount" : "2621","taxAmount" : 5.78572010993958}

I want the output to be something like:

{
    "_id" : "B8 - Göteborg",
    "days" : [ 
        {
            "date" : "2017-03-24",
            "creditAccounts" : [ 
                {
                    "creditAccount" : "3013",
                    "creditAccountSum" : 23.214279174804
                }, 
                {
                    "creditAccount" : "3014",
                    "creditAccountSum" : 53.2142791748047,
                }, 
            ],
            "debitAccounts" : [ 
                {
                    "debitAccount" : "1581",
                    "debitAccountSum" : 52
                }
            ],
            "taxAccounts" : [ 
                {
                    "taxAccount" : "2620",
                    "taxAccountSum" : 2.78572010993958
                },
                {
                    "taxAccount" : "2621",
                    "taxAccountSum" : 5.78572010993958
                }
            ]
        }
    ]
}

That is per placeName and day have a list of all unique creditAccounts, debitAccounts, taxAccounts and their sum. I have managed to do the grouping for one of the fields and it looks like:

db.getCollection('accounting_records').aggregate([
    {$match : {...},  
    {$group: {_id: {placeName :'$placeName',year: {$substr: ['$created',0,4]},month: {$substr: ['$created',5,2]},dayOfMonth: {$substr: ['$created',8,2]},creditAccount:'$creditAccount'}, created: {$max: '$created'},records: { $addToSet: "$$ROOT" }}},
    {$sort : {created : 1}},
    {$group: {_id: {placeName :'$_id.placeName',year: '$_id.year',month: '$_id.month',dayOfMonth: '$_id.dayOfMonth'},created: {$max: '$created'}, creditAccounts:{$push :{creditAccount: '$_id.creditAccount',sum : {$sum: '$records.creditAmount'}, creditAccountName:'$records.creditAccountName'}}}},
    {$sort : {created : 1}},
    {$group : {_id : '$_id.placeName', created: {$max: '$created'}, days : {$push: {date: {$concat: ['$_id.year','-','$_id.month','-','$_id.dayOfMonth']},creditAccounts : '$creditAccounts'}}}},
    {$sort : {_id : 1}},
])

But I have no idea how to manage to do the grouping for all 3 creditAccount, taxAccount & debitAccount. Perhaps it's possible with mapReduce but haven't looked in to that since I've read performance is much worse compared to using aggregations framework.

1 Answers
Related