'How can $sum data by $lookup filed in $group query with mongo

Here my query:

db.A.aggreagte([
  {
    $lookup: {
      from: 'B',
      localField: '_id',
      foreignField: 'aId',
      as: 'B'
    }
  },
  {
    $group: {
      _id: 'cId',
      groupedData: {
        name: '$name',
        bData: {
          $arrayElemAt: ['$B', 0]
        }
      }
    }
  }
])

In B data, I have an Int field { level: 1 } the level is enum [1, 2, 3]

I want to create 3 new attribute to get number of groupedData.bData.level = 1 or 2 or 3

collection A:

[
  {
    _id: 'A1',
    name: 'aName_1',
    cId: 'C1'
  },
  {
    _id: 'A2',
    name: 'aName_2',
    cId: 'C1'
  },

  {
    _id: 'A3',
    name: 'aName_3',
    cId: 'C2'
  }
]

collection B:

[
  {
    _id: 'B1',
    level: 1,
    aId: 'A1'
  },
  {
    _id: 'B2',
    level: 2,
    aId: 'A2'
  },
  {
    _id: 'B3',
    level: 1,
    aId: 'A2'
  },
  {
    _id: 'B4',
    level: 3,
    aId: 'A1'
  },
  {
    _id: 'B5',
    level: 3,
    aId: 'A3'
  }
]

Expect: [ { _id: 'C1', groupedData: [{ name: 'aName_1', }, { name: 'aName_2', }], numLevel1: 2, numLevel2: 1, numLevel3: 3 }, { _id: 'C2', groupedData: [{ name: 'aName_3', }], numLevel3: 3 } ]



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source