How to extract grouped results from array in $group stage and return as separate fields?-CodePudding

I'm running an aggregation query, and the $group stage is as follows

$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 }
      }

It returns the correct results, and returns all the grouped results in the _id:{} array.

I now want to extract all those fields from the array and return them not within the array so I can more easily export the output to a spreadsheet.

I tried using this stage:

{
     $project:
     {
         year_month: 1
         ,client_name: 1
         ,client_label: 1
         ,client_code: 1
         ,client_country: 1
         ,base_curr: 1
         ,inv_curr: 1         
         ,dest_curr: 1
         ,total_vol: 1
         ,total_tran : 1
     }
},

But that returned the same results as the $group stage:

{
    "_id" : {
        "year_month" : "2022-01",
        "client_name" : "client A",
        "client_label" : "client A",
        "client_code" : NumberInt(0000),
        "client_country" : "TH",
        "base_curr" : "USD",
        "inv_curr" : "USD",
        "dest_curr" : "HKD"
    },
    "total_vol" : 100000,
    "total_tran" : 100.0
}

I want the "year_month" through "dest_curr" fields at the same level as the "total_vol" and "total_tran", so that when the data is exported they all appear as separate columns (now it's all captured as one "_id" column, and a "total_vol" and "total_tran" column). What's the best way to do this?

CodePudding user response：

From a terminology perspective, you currently have an embedded document (or nested fields) rather than an array.

The straightforward way to do this is to simply enumerate each field, eg:

"year_month": "$_id.year_month",

There are fancier ways to do this, but as you only have a handful of fields this should suffice. Working playground example here.

Edit

An alternative ("fancier") approach is to leverage the $replaceWith stage using the $mergeObjects operator inside of it. Then you can $unset the previous _id field afterwards. It would look like this:

db.collection.aggregate([
  {
    "$replaceWith": {
      "$mergeObjects": [
        "$$ROOT",
        "$_id"
      ]
    }
  },
  {
    $unset: "_id"
  }
])

Playground link here

I also fixed the earlier playground link that had a typo for the client_label field.