Aggregate monthly results from transaction data-CodePudding

I'm a newbie at MongoDB, but have a strong SQL background.

The data I'm working with is a transactions collection, to be joined with a clients collection. The transactions collection contains every single transaction placed by our clients, could in theory be multiple transactions per day.

I want to roll those transactions up to a month-end date and sum all transaction amounts (USD_Value) to get total volume, as well as count every individual transaction to get total # of transactions in a month. This then also needs to be grouped by client and a few other fields, for analytical purposes.

So far I've built this query in mongodb:

db.transactions.aggregate([
{
     $lookup:
     {
        from: 'clients',
        localField: 'client',
        foreignField: '_id',
        as: 'clients_docs'
     }
},
{
    $match:
    {
        clients_docs: { $ne: [] }
    }
},
{
     $addFields:
     {
         clients_docs:
         {
             $arrayElemAt: ["$clients_docs", 0]
         }
     }   
},
{
    $match:
    {
        $and:
        [
            { "updated_at": { $gte: ISODate("2022-01-01") } },
            { "updated_at": { $lte: ISODate("2022-01-31") } },
            
        ]
        ,"status": { $eq: "PAID" }
        ,"clients_docs.is_send_client": { $eq: true }
    }    
},
{
      $group:
      {
          _id: 
          { 
              year_month: { $dateToString: { "date": "$updated_at", "format": "%Y-%m"  } } 
              ,client_name: "$clients_docs.client_name"
              ,client_label: "$clients_docs.client_label"
              ,client_code: "$clients_docs.client_code"
              ,client_country: "$clients_docs.client_country"
              ,base_curr: "$clients_docs.client_base_currency"
              ,inv_curr: "$clients_docs.client_invoice_currency" 
              ,dest_curr: "$store.destination_currency"         
              ,total_vol: { $sum: "$USD_Value" }
              ,total_tran: { $sum: 1 }
          }
      }                 
},
{
    $limit: 10
}
])

This returns this result:

{
    "_id" : {
        "year_month" : "2022-01",
        "client_name" : "company A",
        "client_label" : "company A",
        "client_code" : NumberInt(0000),
        "client_country" : "BR",
        "base_curr" : "USD",
        "inv_curr" : "USD",
        "dest_curr" : "USD",
        "total_vol" : 1000.00,
        "total_tran" : 1.0
    }
}

That's the result for just one transaction, not all transactions in that month for that client, aggregated.

It seems that in my attempt to get a YYYY-MM date format, it's just converting "$updated_at" to YYYY-MM format, but then not actually grouping by that roll-up and still just returning every individual transaction. What should I do differently to get the correct results?

To me this would have been relatively straightforward to do in SQL.

CodePudding user response：

The accumulators (that generate total_vol and total_tran) should be outside of the grouping fields (_id).

Try changing your $group to:

{
      $group:
      {
          _id: 
          { 
              year_month: { $dateToString: { "date": "$updated_at", "format": "%Y-%m"  } } 
              ,client_name: "$clients_docs.client_name"
              ,client_label: "$clients_docs.client_label"
              ,client_code: "$clients_docs.client_code"
              ,client_country: "$clients_docs.client_country"
              ,base_curr: "$clients_docs.client_base_currency"
              ,inv_curr: "$clients_docs.client_invoice_currency" 
              ,dest_curr: "$store.destination_currency"                      
          },
          total_vol: { $sum: "$USD_Value" }
         ,total_tran: { $sum: 1 }
      }                 
}