How to calculate total cost for an Organization when they call the API?

Question

I have designed a collection named Organization as follows: with name is the name of the organization, desc is a description of the organization, id_app is an array of applications for that organization

1- Below is Organization

{
    "_id" : ObjectId("64b4b8bc3975804004fa81b4"),
    "id_app" : [
        ObjectId("64b4b842a5831248e8a6b65c"),
        ObjectId("64b4b34f54106c24dc018992")
    ],
    "is_deleted" : false,
    "name" : "Org1",
    "desc" : "Org1 desc",
    "createdAt" : ISODate("2023-07-17T03:42:52.845+0000"),
    "updatedAt" : ISODate("2023-07-17T03:42:52.845+0000"),
    "__v" : NumberInt(0)
}

2- Bellow is Application collection:

with id_api is an array of the apis that application calls

{
    "_id" : ObjectId("64b4b34f54106c24dc018992"),
    "id_api" : [
        ObjectId("64b4ac07c4784418986870dc"),
        ObjectId("64b4abcdc4784418986870d7")
    ],
    "is_deleted" : false,
    "name" : "app1",
    "desc" : "app1 desc",
    "createdAt" : ISODate("2023-07-17T03:19:43.573+0000"),
    "updatedAt" : ISODate("2023-07-17T03:19:43.573+0000"),
    "__v" : NumberInt(0)
}

3- Bellow is Apis collection:

with name is the API name, desc is the api description, URL is the path of the API and field is the field of that api, for example education api.

{
    "_id" : ObjectId("64b4abcdc4784418986870d7"),
    "is_deleted" : false,
    "name" : "api1",
    "desc" : "Api 1 desc",
    "URL" : "http://localhost:8080/api/api1",
    "field" : "edu",
    "createdAt" : ISODate("2023-07-17T02:47:41.849+0000"),
    "updatedAt" : ISODate("2023-07-17T02:47:41.849+0000"),
    "__v" : NumberInt(0)
}

Each time an app calls an API, a fee will be generated (eg cost1 = 10$ ), and a collection named Billing will be created.

4- And below is Billing collection

{
    "_id" : ObjectId("64b4c3d6068f5537e4bbd01b"),
    "is_deleted" : false,
    "cost1" : NumberInt(10),
    "cost2" : NumberInt(5),
    "cost3" : NumberInt(3),
    "id_app" : ObjectId("64b4b842a5831248e8a6b65c"),
    "createdAt" : ISODate("2023-07-17T04:30:14.646+0000"),
    "updatedAt" : ISODate("2023-07-17T04:30:14.646+0000"),
    "__v" : NumberInt(0)
}

While there are Organation, Application, Apis, and Billing collections. So the question is, how to calculate the billing amount (total cost) that an organization is charged when their apps call the API.

Thanks you so much

Answer 1

1. You can app ids from organization collection using organization id

    db.getCollection('organization').find({_id: ObjectId("64b4b8bc3975804004fa81b4")});

2. And next you can use aggregate using application ids

    db.getCollection('Billing').aggregate([
        {
            $match: {
                id_app: {
                    $in: [ObjectId("64b4b842a5831248e8a6b65c"), ObjectId("64b4b34f54106c24dc018992")
                    ]
                }
            }
        },
        {
            $group: {
                _id: null, totalCost: {
                    $sum: "$cost1"
                }
            }
        }
    ]);

3. The result

    {
        "_id" : null,
        "totalCost" : 10
    }

4. You can use following query to calculate total cost in a unique query

 db.getCollection('Billing').aggregate([
        {
            $match: {
                id_app: {
                    $in: [ObjectId("64b646f602dbe6f481912d85"), ObjectId("64b4b34f54106c24dc018992")
                    ]
                }
            }
        },
        {
            $project: {
                totalCost: {
                    $sum: ["$cost1", "$cost2", "$cost3"]
                }
            }
        }
    ]);