How to aggregate count on three levels?

Working with Data
1

How can I use aggregate to create a two level aggregation?

orders: [
    { id: 100, status: 3, strDate: '2021-03-01', items: [], strStatus: 'shipped' },
    { id: 101, status: 2, strDate: '2021-04-01', items: [], strStatus: 'packed' },
    { id: 102, status: 1, strDate: '2021-04-01', items: [], strStatus: 'ordered' },
]

result: [
    {
        _id: '2021-03-01', orders: [
            { _id: 'shipped', count: 1 }
        ]
    },
    {
        _id: '2021-04-01', orders: [
            { _id: 'packed', count: 1 },
            { _id: 'ordered', count: 1 }
        ]
    },
]

Thanks,
bluepuma

2

Hello @blue_puma,

You can use the aggregate’s $group stage to get the desired result; see $group - Pivot Data Example .

3

Thanks, I still can not wrap my head around it. Is there a more generic approach or “formula” how to handle this?


// aggregate count on 3 levels

items: [
    { id: 1, level1: 'A', level2: 'A1', level3: 'A1x' },
    { id: 2, level1: 'A', level2: 'A1', level3: 'A1x' },
    { id: 3, level1: 'A', level2: 'A2', level3: 'A1x' },
    { id: 4, level1: 'B', level2: 'B1', level3: 'B1x' },
    { id: 5, level1: 'B', level2: 'B1', level3: 'B1x' },
    { id: 6, level1: 'B', level2: 'B1', level3: 'B1y' },
]

results: [
    {
        id: 'A',
        ag: [
            {
                id: 'A1',
                ag: [
                    {
                        id: 'A1x',
                        count: 2
                    }
                ]
            },
            {
                id: 'A2',
                ag: [
                    {
                        id: 'A1x',
                        count: 1
                    }
                ]
            }
        ]
    },
    {
        id: 'B',
        ag: [
            {
                id: 'B1',
                ag: [
                    {
                        id: 'B1x',
                        count: 2
                    },
                    {
                        id: 'B1y',
                        count: 1
                    }
                ]
            },
        ]
    },
]

Even better would be a count on every level :grinning:


results: [
    {
        id: 'A',
        count: 3,
        ag: [
            {
                id: 'A1',
                count: 2,
                ag: [
                    {
                        id: 'A1x',
                        count: 2
                    },
                ],
            },
            {
                id: 'A2',
                count: 1,
                ag: [
                    {
                        id: 'A1x',
                        count: 1
                    }
                ],
            }
        ],
    },
    {
        id: 'B',
        count: 3,
        ag: [
            {
                id: 'B1',
                count: 3,
                ag: [
                    {
                        id: 'B1x',
                        count: 2
                    },
                    {
                        id: 'B1y',
                        count: 1
                    }
                ],
            },
        ],
    },
]
4

Multiple levels of aggregation require applying the grouping multiple times. Here is an example of such a query:

2 Likes
5

Awesome @Prasad_Saya, thanks a lot. That was exactly what I was looking for.

Here is the adapted solution:

db.collection.aggregate([
  {
    $group: {
      _id: {
        level1: "$level1",
        level2: "$level2",
        level3: "$level3"
      },
      count: {
        $sum: 1
      }
    }
  },
  {
    $group: {
      _id: {
        level1: "$_id.level1",
        level2: "$_id.level2"
      },
      count: {
        $sum: "$count"
      },
      data: {
        $push: {
          _id: "$_id.level3",
          count: "$count",
          data: "$data"
        }
      }
    }
  },
  {
    $group: {
      _id: "$_id.level1",
      count: {
        $sum: "$count"
      },
      data: {
        $push: {
          _id: "$_id.level2",
          count: "$count",
          data: "$data"
        }
      }
    }
  }
])

MongoDB Playground

1 Like
6

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.