How to use Mongodb aggregate group to count the size of a nested array?

Working with Data
, , ,
1

I currently have an appointments booking system for clinics on my ReactJS website supported by NodeJS and ExpressJS as my server.
I am trying to calculate the amount paid, number of appointments and number of employees which services were rendered to for each month.

I have the following code

const analytics = await appointments
        .aggregate([
          {
            $unwind: "$details",
          },
          {
            $unwind: "$details.employees",
          },
          {
            $unwind: "$payment",
          },
          {
            $group: {
              _id: {
                month: {
                  $month: {
                    $dateFromString: {
                      dateString: "$details.date",
                      format: "%Y-%m-%d",
                    },
                  },
                },
                year: {
                  $year: {
                    $dateFromString: {
                      dateString: "$details.date",
                      format: "%Y-%m-%d",
                    },
                  },
                },
              },
              employees: { $sum: { $size: "$details.employees" }},
              amountMade: { $sum: "$payment.amount" },
              count: { $sum: 1 },
            },
          },
          { $sort: { "_id.month": 1 } },
        ])
        .toArray();

This does not work as expected, I keep on getting errors such as an error that tells me $size is not an operator for group.

I am not going to post a specific error message but what I would like is some help which would tell me how I can accomplish what I am trying to achieve

Here is an example document copied from my atlas db

{
    "_id": {
        "$oid": "6391da7061126c0016580b9d"
    },
    "details": {
        "company": {
            "id": "M70120",
            "name": "My Clinic"
        },
        "date": "2022-12-09",
        "purchaseOrderNumber": "435476657",
        "clinic": "Churchill",
        "ndaAccepted": true,
        "employees": [
            {
                "id": "bUfj8N3hhZ3dqo3A9HGLAE",
                "name": "Someone",
                "idNumber": "88788758751",
                "comments": [],
                "occupation": "Worker",
                "services": [
                    {
                        "price": {
                            "$numberDouble": "37.43"
                        },
                        "id": "cannabis"
                    },
                    {
                        "price": {
                            "$numberInt": "445"
                        },
                        "id": "clearance"
                    }
                ],
                "sites": [
                    {
                        "id": "kxnhmvU1UqFcnUxFUMHGNQ",
                        "name": "Proud Mines",
                        "hasAccessCard": true
                    }
                ]
            }
        ]
    },
    "usersWhoCanEdit": [],
    "usersWhoCanManage": [
        {
            "id": "DAV17421",
            "name": "David  Davies"
        }
    ],
    "payment": {
        "proofOfPayment": "",
        "amount": {
            "$numberDouble": "517.4300000000001"
        }
    },
    "isVoided": false,
    "isComplete": true,
    "messages": [
        {
            "message": "Hi My Clinic team",
            "author": {
                "id": "ADM81947",
                "name": "Admin ",
                "role": "admin"
            },
            "createdAt": "2022-12-08 14:41:02"
        },
        {
            "message": "",
            "author": {
                "id": "ADM81947",
                "name": "Admin ",
                "role": "admin"
            },
            "createdAt": "2022-12-08 14:41:02"
        }
    ],
    "status": "approved",
    "id": "WILI751191CHU",
    "invoice": {
        "id": "506dada3-d92a-4ee8-bc23-0a7129196c24",
        "amount": {
            "$numberDouble": "517.4300000000001"
        },
        "date": "2022-12-08T12:39:29.788Z"
    }
}

with the js code I want to get some aggregate analytics data in the form

[
  { _id: { month: 2, year: 2023 }, amountMade: 179.2, count: 2, employeesCaterdTo: 10, servicesRendered: 3 },
  { _id: { month: 3, year: 2023 }, amountMade: 179.2, count: 2, employeesCaterdTo: 5, servicesRendered: 8 },
  { _id: { month: 4, year: 2023 }, amountMade: 7130, count: 5,employeesCaterdTo: 2, servicesRendered: 9 }
]
2

First thing that jumps to the eyes is that you $unwind details but details is not an array.

I would correct that and try again.

1 Like