Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Mongodb Trying to get selected fields to return from aggregate

I'm having trouble with my aggregate function. I'm trying to get the users most common orders from the database but I'm only returning the name and the count. I've tried using the $project operator but I can't seem to make it return anything other than what's in the $group statement.

Here is my current aggregate function:

OrderModel.aggregate(
        {$unwind: "$products"},
        {$match: { customerID: customerID }},
        {$group: { _id: "$products.name", count: {$sum:1}}},
        {$project: {name: "$_id", _id:0, count:1, active:1}},
        {$sort: {"count" : -1}},
        {$limit: 25 })

This just produces an output as follows {"count":10, "name": foo"} whereas I want to return the whole object; embedded docs and all. Any ideas where I'm going wrong?

Edit- Added example document and expected output

Document:

{
    "charge": {},
        "captured": true,
        "refunds": [
        ],
        "balance_transaction": "txn_104Ics4QFdqlbCVHAdV1G2Hb",
        "failure_message": null,
        "failure_code": null,
        "amount_refunded": 0,
        "customer": "cus_4IZMPAIkEdiiW0",
        "invoice": null,
        "dispute": null,
        "statement_description": null,
        "receipt_email": null
    },
    "total": 13.2,
    "userToken": "cus_4IZMPAIkEdiiW0",
    "customerID": "10152430176375255",
    "_id": "53ad927ff0cb43215821c649",
    "__v": 0,
    "updated": 20140701082928810,
    "created": 20140627154919216,
    "messageReceived": false,
    "ready": true,
    "active": false,
    "currency": "GBP",
    "products": [
        {
            "name": "Foo",
            "active": true,
            "types": [
                {
                    "variants": [
                        {
                            "name": "Bar",
                            "isDefault": false,
                            "price": 13.2
                        }
                    ]
                }
            ]
        }
    ]
}

Expected outcome:

[
    {
        "name": "Foo",
        "active": true,
        "types": [
            {
                "variants": [
                    {
                        "name": "Bar",
                        "isDefault": false
                    }
                ]
            },
            {
                "variants": [
                    {
                        "name": "Something else",
                        "isDefault": false
                    }
                ]
            }
        ],
        "quantity": 10
   },
   {
       "name": "Another product",
       "active": true,
       "types": [
           {
               "variants": [
                   {
                       "name": "Bar",
                       "isDefault": false
                   }
               ]
           }
       ],
       "quantity": 7
   }

]

Thanks!

like image 634
donpisci Avatar asked Jul 04 '14 10:07

donpisci


People also ask

How do I get only certain fields in MongoDB?

You can select a single field in MongoDB using the following syntax: db. yourCollectionName. find({"yourFieldName":yourValue},{"yourSingleFieldName":1,_id:0});

What does MongoDB aggregation return?

aggregate() method returns a cursor to the documents produced by the final stage of the aggregation pipeline operation, or if you include the explain option, the document that provides details on the processing of the aggregation operation.

What does $project do in MongoDB?

The $project takes a document that can specify the inclusion of fields, the suppression of the _id field, the addition of new fields, and the resetting of the values of existing fields. Alternatively, you may specify the exclusion of fields. Specifies the inclusion of a field.


1 Answers

Largely speaking here, $project relies on the "absolute path" to the field property in the document on the "right hand" side. Shortcuts such as 1 are just for where that element is actually the top level of the document.

Also you need to be able to retain fields when you $group, so this is where you use various grouping operators such as $first and $addToSet or $push to keep the information you are puling from the inner array. And you must $unwind twice here as well since you are combining "types" across documents, and you do not want just the $first in this case.

OrderModel.aggregate([
    { "$unwind": "$products" },
    { "$unwind": "$products.types" },
    { "$group": {
        "_id": "$products.name",
        "active": { "$first": "$products.active" },
        "types": { "$addToSet": "$products.types" },
        "quantity": { "$sum": 1 }
    }},
    { "$project": {
        "_id": 0,
        "name": "$_id",
        "active": 1,
        "types": 1,
        "quantity": 1
    }}
],function(err,results) {

});
like image 160
Neil Lunn Avatar answered Oct 05 '22 07:10

Neil Lunn