How to perform multiple group bys in a aggregation query

Working with Data
2

Hello, @Sean_Barry! Welcome to the community!

You can achieve what you want with two sequential $group stages

db.orders.aggregate([
  {
    $group: {
      _id: {
        country: '$data.nested.country',
        product: '$data.product',
      },
      productCodes: {
        $push: '$data.latest.sub.code',
      },
      uniqueCodes: {
        $addToSet: '$data.latest.sub.code',
      }
    }
  },
  {
    $group: {
      _id: '$_id.country',
      country: {
        $first: '$_id.country',
      },
      products: {
        $push: {
          product: '$_id.product',
          codes: {
            $map: {
              // run $filter+$size operations per each code
              input: '$uniqueCodes',
              as: 'code',
              in: {
                code: '$$code',
                count: {
                  $size: {
                    $filter: {
                      // collect same codes into one array
                      // to be able to count them per product 
                      input: '$productCodes',
                      cond: {
                        $eq: ['$$code', '$$this'],
                      },
                    },
                  },
                },
              },
            },
          },
        },
      },
    },
  },
  // cleanup
  {
    $unset: ['_id'],
  }
]);

Sample dataset:

db.orders.insertMany([
  {
    '_id' : 1,
    'data' : {
      'nested' : {
        'country' : 'France'
      },
      'product' : 'Product 1',
      'latest' : {
        'sub' : {
          'code' : 'Code A'
        }
      }
    }
  },
  {
    '_id' : 2,
    'data' : {
      'nested' : {
        'country' : 'France'
      },
      'product' : 'Product 2',
      'latest' : {
        'sub' : {
          'code' : 'Code B'
        }
      }
    }
  },
  {
    '_id' : 3,
    'data' : {
      'nested' : {
        'country' : 'Canada'
      },
      'product' : 'Product 1',
      'latest' : {
        'sub' : {
          'code' : 'Code B'
        }
      }
    }
  },
  {
    '_id' : 4,
    'data' : {
      'nested' : {
        'country' : 'Ukraine'
      },
      'product' : 'Product 2',
      'latest' : {
        'sub' : {
          'code' : 'Code B'
        }
      }
    }
  },
  {
    '_id' : 5,
    'data' : {
      'nested' : {
        'country' : 'Canada'
      },
      'product' : 'Product 1',
      'latest' : {
        'sub' : {
          'code' : 'Code A'
        }
      }
    }
  },
  {
    '_id' : 6,
    'data' : {
      'nested' : {
        'country' : 'Canada'
      },
      'product' : 'Product 1',
      'latest' : {
        'sub' : {
          'code' : 'Code A'
        }
      }
    }
  }
]);

Aggregation output on sample dataset:

[
  {
    "country" : "France",
    "products" : [
      {
        "product" : "Product 2",
        "codes" : [
          {
            "code" : "Code B",
            "count" : 1
          }
        ]
      },
      {
        "product" : "Product 1",
        "codes" : [
          {
            "code" : "Code A",
            "count" : 1
          }
        ]
      }
    ]
  },
  {
    "country" : "Canada",
    "products" : [
      {
        "product" : "Product 1",
        "codes" : [
          {
            "code" : "Code B",
            "count" : 1
          },
          {
            "code" : "Code A",
            "count" : 2
          }
        ]
      }
    ]
  },
  {
    "country" : "Ukraine",
    "products" : [
      {
        "product" : "Product 2",
        "codes" : [
          {
            "code" : "Code B",
            "count" : 1
          }
        ]
      }
    ]
  }
]
1 Like