mongodb php query in documents with nested objects

Question

So here is a sample of a document in my mongodb collection:

{
    "_id" : ObjectId("561e0de61c9218b7bf9877c3"),
    "Date" : NumberLong(20151014),
    "Hour" : NumberLong(10),
    "ProductId" : ObjectId("5614ba9c2e131caa098b4567"),
    "ProductName" : "Test",
    "ProducerId" : ObjectId("5617802151f8adf4db329d52"),
    "ProducerName" : "Producer",
    "ProducerRate" : NumberLong(300),
    "ProducerMedium" : "Emailer",
    "TotalLead" : NumberLong(6),
    "VerifiedLead" : NumberLong(3),
    "UnverifiedLead" : NumberLong(2),
    "UnQualifiedLead" : NumberLong(1),
    "TotalEarning" : NumberLong(660),
    "Consumers" : [ 
        {
            "ConsumerId" : ObjectId("5617802151f8adf4db329d54"),
            "ConsumerName" : "Consumer1",
            "ConsumedRate" : NumberLong(120),
            "ConsumedLead" : NumberLong(3),
            "Earning" : NumberLong(360)
        }, 
        {
            "ConsumerId" : ObjectId("5617802151f8adf4db329d58"),
            "ConsumerName" : "Consumer2",
            "ConsumedRate" : NumberLong(100),
            "ConsumedLead" : NumberLong(3),
            "Earning" : NumberLong(300)
        }
    ]
}

Now i want to get the ConsumedLead grouped by ConsumerId and ProductId from the database in php.

what i have did so far to give me TotalLead and VerifiedLead grouped by product id but have no idea how to get consumerbased results for same:

$keyf = new MongoCode('function(doc) {      
                return {\'ProductId\': doc.ProductId,\'ProductName\': doc.ProductName};
        }');
       $initial = array('TotalLead'=>0,'VerifiedLead'=>0);

        $reduce = "function(obj, prev) {
        prev.TotalLead += obj.TotalLead;
        prev.VerifiedLead += obj.VerifiedLead;

        }";
        $result = $collection->group($keyf, $initial, $reduce);
         var_dump($result);

Any Help Please.

EDIT: expected result wpuld be :

{ [0]=> array(4) { ["ProductId"]=> object(MongoId)#8 (1) { ["$id"]=> string(24) "5614ba9c2e131caa098b4567" } ["ProductName"]=> string(4) "Test" ["ConsumerId"]=> object(MongoId)#8 (1) { ["$id"]=> string(24) "5617802151f8adf4db329d58" } ["ConsumedLead"]=> float(4) } }

chridam · Accepted Answer · 2015-10-15 08:06:24Z

The solution is to use the aggregation framework where the operation includes an $unwind operator initial pipeline stage as this will deconstruct the Consumers array field from the input documents and outputs a document for each element. Each output document replaces the array with an element value. This will then make it possible for the $sum group accumulator operator in the $group step to work and thus givies you the required ConsumedLead grouped by ConsumerId and ProductId:

db.collection.aggregate([
    {
        "$unwind": "$Consumers"
    },
    {
        "$group": {
            "_id": {
                "ProductId": "$ProductId",
                "ConsumerId": "$Consumers.ConsumerId"
            },
            "TotalConsumedLead": {
                "$sum": "$Consumers.ConsumedLead"
            }
        }
    }
])

Running this aggregation operation on the above sample will result:

/* 0 */
{
    "result" : [ 
        {
            "_id" : {
                "ProductId" : ObjectId("5614ba9c2e131caa098b4567"),
                "ConsumerId" : ObjectId("5617802151f8adf4db329d58")
            },
            "TotalConsumedLead" : NumberLong(3)
        }, 
        {
            "_id" : {
                "ProductId" : ObjectId("5614ba9c2e131caa098b4567"),
                "ConsumerId" : ObjectId("5617802151f8adf4db329d54")
            },
            "TotalConsumedLead" : NumberLong(3)
        }
    ],
    "ok" : 1
}

So your final working aggregation in PHP should be:

$pipeline = array(    
    array('$unwind' => '$Consumers'),
    array(
        '$group' => array(
            '_id' => array(
                'ProductId' => '$ProductId',
                'ConsumerId' => '$Consumers.ConsumerId',
            ),
            'TotalConsumedLead' => array(
                '$sum' => '$Consumers.ConsumedLead'
            ),
        )
    ),
);

$out = $collection->aggregate($pipeline ,$options);

Collectives™ on Stack Overflow

mongodb php query in documents with nested objects

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related