CODEWITHSUNDEEP
mongodbmongoosemongodb-query
asotos
asotos

Reputation: 337

Group items inside of another group

I want to group the following collection by category and sum its total value, then create a subcategory attribute, on same structure, summing subcategories if more than one equal.

[
  {
    "_id": 1,
    "date": ISODate("2019-10-10T00:00:00.000Z"),
    "description": "INTERNET BILL",
    "credit": "",
    "debit": "-100.00",
    "category": "home",
    "subcategory": "internet",
    "__v": 0
  },
  {
    "_id": 2,
    "date": ISODate("2019-10-10T00:00:00.000Z"),
    "description": "WATER BILL",
    "credit": "",
    "debit": "-150.00",
    "category": "home",
    "subcategory": "water",
    "__v": 0
  },
  {
    "_id": 3,
    "date": ISODate("2019-10-10T00:00:00.000Z"),
    "description": "MC DONALDS",
    "credit": "",
    "debit": "-30.00",
    "category": "food",
    "subcategory": "restaurants",
    "__v": 0
  },
{
    "_id": 4,
    "date": ISODate("2019-10-10T00:00:00.000Z"),
    "description": "BURGER KING",
    "credit": "",
    "debit": "-50.00",
    "category": "food",
    "subcategory": "restaurants",
    "__v": 0
  },
  {
    "_id": 5,
    "date": ISODate("2019-10-10T00:00:00.000Z"),
    "description": "WALMART",
    "credit": "",
    "debit": "-20.00",
    "category": "food",
    "subcategory": "groceries",
    "__v": 0
  },
]

Desireble output: 

[
  {
    "_id": "home",
    "total": "-250.00",
    "subcategory" : [
     {"id": "internet", "total": "-100"},
     {"id": "water", "total": "-150"}
    ]
  },
  {
    "_id": "food",
    "total": "-100.00",
    "subcategory" : [
      {"id": "restaurants", "total": "-80"},
      {"id": "groceries", "total": "-20"}
    ]
  }
]

With the following query, I've almost achieved it, but I haven't find a way to sum values on subcategories.

db.getCollection('expenses').aggregate([
    {$match:
        {"date" : { "$gte" : new Date("11-10-2019"), "$lte": new Date("2019-10-11") }}
    },
    {$group: {
        _id: "$category",
        total: { $sum: { $toDouble: { $cond: { if: { $ne: [ "$debit", ""] }, then: "$debit", else: "$credit" } } } },
        subcategories: { $addToSet: {id: "$subcategory" }},

   }}
])

Upvotes: 2

Views: 72

Answers (1)

mickl
mickl

Reputation: 49945

You can to $group twice (by subcategory first):

db.collection.aggregate([
    {
        $group: {
            _id: { category: "$category", subcategory: "$subcategory" },
            total: { $sum: { $toDouble: "$debit" } }
        }
    },
    {
        $group: {
            _id: "$_id.category",
            total: { $sum: "$total" },
            subcategories: { $push: { id: "$_id.subcategory", total: "$total" } }
        }
    }
])

Mongo Playground

Upvotes: 1

Related Questions