Grouping from multiple arrays and find sum

user_6720372 0
Viewed 33

I have these documents.

[
    {
        _id: 'a1',
        sales: [
            {
                amont: 12,
                servicer_id: 'A',
            },
            {
                amont: 10,
                servicer_id: 'B',
            },
        ],
        service: [
            {
                amont: 25,
                servicer_id: 'A',
            },
            {
                amont: 20,
                servicer_id: 'B',
            },
        ],
    },
    {
        _id: 'a2',
        sales: [
            {
                amont: 10,
                servicer_id: 'A',
            },
            {
                amont: 20,
                servicer_id: 'B',
            },
        ],
        service: [
            {
                amont: 15,
                servicer_id: 'A',
            },
            {
                amont: 10,
                servicer_id: 'B',
            },
        ],
    },
]

From the above document, I want to find the following.

1.Number of the service done by both servicer A and B.

2.Number of the sales done by both servicer A and B.

3.Total service amount of servicer A and B.

4.Total sales amount of servicer A and B.

Expected Output

[
    {
        servicer: 'A',
        services: 2,
        sales: 2,
        totalSale: 22,
        totalService: 40,
    },
    {
        servicer: 'B',
        services: 2,
        sales: 2,
        totalSale: 30,
        totalService: 30,
    },
];
aggregation-framework mongodb-query mongodb
1 Answers

The approach for this is quite simple, we're going to $group by the service id, and use $sum to calculate all the needed values, like so:

db.collection.aggregate([
  {
    $unwind: {
      path: "$service",
      preserveNullAndEmptyArrays: true
    }
  },
  {
    $group: {
      _id: {
        serviceId: "$service.servicer_id",
        docId: "$_id"
      },
      services: {
        $sum: 1
      },
      sales: {
        $first: "$sales"
      },
      totalService: {
        $sum: "$service.amont"
      },
      
    }
  },
  {
    "$addFields": {
      serviceSales: {
        $filter: {
          input: "$sales",
          as: "sale",
          cond: {
            $eq: [
              "$$sale.servicer_id",
              "$_id.serviceId"
            ]
          }
        }
      }
    }
  },
  {
    $group: {
      _id: "$_id.serviceId",
      services: {
        $sum: "$services"
      },
      sales: {
        $sum: {
          $size: "$serviceSales"
        }
      },
      totalService: {
        $sum: "$totalService"
      },
      totalSale: {
        $sum: {
          $reduce: {
            input: "$serviceSales",
            initialValue: 0,
            in: {
              $sum: [
                "$$value",
                "$$this.amont"
              ]
            }
          }
        }
      }
    }
  }
])

Mongo Playground

user_107703700
Related