MongoDB.live, free & fully virtual. June 9th - 10th. Register Now

How to create maps by attributes with aggregation

I have documents that looks like that:

ref1:     5ec68d5edcf68a016c4d1f68
ref2:     5ec68d5edcf68a016c4d1f68
country:  "US"
date:     2020-05-22T00:00:00.000+00:00
hour:     14
time:     2020-05-22T14:42:11.396+00:00
type:     "x"

which I aggregate by type. my desired outcome is something like:

ref1:    5ec68d5edcf68a016c4d1f68
ref2:    5ec68d5edcf68a016c4d1f68
date:    2020-05-22T00:00:00.000+00:00
contry: {
   US: {x: 1, y: 10 ...},
   CA: ....
   .
   .

},
hour: {
   14: {x: 5, y: 0 ...},
   20: ....
   .
   .

}

or alternatively to create a document for each ‘attribute’:

ref1:          5ec68d5edcf68a016c4d1f68
ref2:          5ec68d5edcf68a016c4d1f68
date:          2020-05-22T00:00:00.000+00:00
aggregatedBy:  'country'
res: {
   US: {x: 1, y: 10 ...},
   CA: ....
   .
   .

}

currently I have $group that aggregates only per ref1,ref2,date and looks like:

{
  _id: { date : '$date', ref1: '$ref1', ref2: '$ref2' },
  x: {
    $sum: {$cond: [{$eq: ['$type', 'x']}, 1, 0]}
  },
  y: {
    $sum: {$cond: [{$eq: ['$type', 'y']}, 1, 0]}
  },
}

I have no clue on how to apply that deeper level.

Thanks

Hello @Dor_Golan,

How to create maps by attributes with aggregation

This is mostly, a series of grouping and projections. I have an example and this mainly uses the aggregation operator $arrayToObject to create the maps.

I have used a somewhat simpler version of the documents, for brevity. Here are some sample documents followed by the query and the output.

{
        "ref" : 1,
        "date" : "2020-05-22T00:00:00.000+00:00",
        "country" : "US",
        "hour" : 14,
        "type" : "x"
}
{
        "ref" : 1,
        "date" : "2020-05-22T00:00:00.000+00:00",
        "country" : "US",
        "hour" : 15,
        "type" : "y"
}
{
        "ref" : 1,
        "date" : "2020-05-22T00:00:00.000+00:00",
        "country" : "CA",
        "hour" : 16,
        "type" : "y"
}
{
        "ref" : 2,
        "date" : "2020-05-22T00:00:00.000+00:00",
        "country" : "RU",
        "hour" : 17,
        "type" : "x"
}
{
        "ref" : 1,
        "date" : "2020-05-22T00:00:00.000+00:00",
        "country" : "US",
        "hour" : 14,
        "type" : "y"
}

The Aggregation:

db.groups.aggregate([
  { 
      $group: { 
           _id: { ref: "$ref", date: "$date", country: "$country", type: "$type" },
           sum: { $sum: 1 }
      }
  },
  { 
      $group: { 
          _id: { ref: "$_id.ref", date: "$_id.date", country: "$_id.country" },
          sums: { $push: { k: "$_id.type", v: "$sum"  } }
      }
  },
  { 
      $addFields: {
          k: "$_id.country",  v: { $arrayToObject: "$sums" }
      }
  },
  { 
      $project: { 
          ref: "$_id.ref", 
          date: "$_id.date", 
          country: { $arrayToObject: [ [ { k: "$k", v: "$v" } ] ] }, 
          _id: 0 
      } 
  },
  { 
      $group: { 
          _id: { ref: "$ref", date: "$date" },
          country: { $push: "$country" }
      }
  },
  { 
      $project: { 
          ref: "$_id.ref", 
          date: "$_id.date", 
          country: 1, 
          _id: 0 
      }
  }
])

The Output:

{
        "ref" : 1,
        "date" : "2020-05-22T00:00:00.000+00:00",
        "country" : [
                {
                        "US" : {
                                "x" : 1,
                                "y" : 2
                        }
                },
                {
                        "CA" : {
                                "y" : 1
                        }
                }
        ]
}
{
        "ref" : 2,
        "date" : "2020-05-22T00:00:00.000+00:00",
        "country" : [
                {
                        "RU" : {
                                "x" : 1
                        }
                }
        ]
}