How to perform a complex query in MongoDB

Question

I want to perform a query on this schema:

{ 
   "name" : "xxx",
   "guests" : [ 
       {
            "category" : "category 1",
            "arrived" : false
       },
       {
            "category" : "category 2",
            "arrived" : false
       },
       {
            "category" : "category 1",
            "arrived" : true
       },
       {
            "category" : "category 2",
            "arrived" : true
       }
   ]
}

I want to get for a certain name, a list with the percentage of guests arrived for each category.

For the document above as example, I want to receive:

{
    name : "xxx",
    results : [
         {
              category : "category 1",
              arrived : 50 (percent)
         },
         {
              category : "category 2",
              arrived : 50 (percent)
         },
    ]

Is there a way to do this with a single MongoDB query? Also, should I do this computation in the client-side or server-side?

Answer 1

You can using aggregate. Here's an example:

[
    {
        $match: {name:"xxx"}
    },
    {
        $unwind: "$guests"
    },
    {
        $group: {
            _id:"$guests.category",
            guests_arrived: {
                $sum: { $cond: [ {$eq:["$guests.arrived",true]}, 1, 0 ] }
            },
            guests_total: {
                $sum: 1
            }
        }
    },
    {
        $project:{
            _id: false,
            category: "$_id",
            guests_arrived:true,
            guests_total:true,
            "percent": {
                $multiply: [
                    100,
                    {$divide: ["$guests_arrived","$guests_total"]}
                ]

            }
        }
    }
]

Explanation of the aggregation pipeline stages used:

$match the on name, since you want results only for a specific name

$unwind the guests array so that we can perform aggregate functions on its members in the next stage

$group by category in order to $sum total guests and the number of guests who have arrived $cond/$eq is used to evaluate arrived and only add to guests_arrived if arrived=true

$project with the primary purpose of computing the percentage based on the aggregate values, guests_arrived and guests_total, from the previous stage