CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
JoshJoe
JoshJoe

Reputation: 1630

Mongodb aggregate field total incorrect

Basically I'm trying to find all the documents that don't match a text search, then do some math on the document fields to return the total. Here is what I've tried to do, but it doesn't work. 

db.my_collection.aggregate(
  [
    { $match: { $text: { $search: "annual one-time One_time monthly quarterly" } } },
    { $project: { _id: 0, score: { $meta: "textScore" } } },
    { $group: { _id: {score: "$score"},  
       totalAmount: { $sum: { $divide: [ { $add: [ "$amount_in_cents" ] }, 100 ] } },       
       count: { $sum: 1 }
    } }

  ]);

Here is what I get. 

[
{
    "_id" : {
        "score" : 0.7500000000000000
    },
    "totalAmount" : 0,
    "count" : 1.0000000000000000
},
{
    "_id" : {
        "score" : 1.0000000000000000
    },
    "totalAmount" : 0,
    "count" : 57.0000000000000000
},
{
    "_id" : {
        "score" : 1.5000000000000000
    },
    "totalAmount" : 0,
    "count" : 7.0000000000000000
}
]

How do I get the totalAmount to be accurate for each score grouping and how do I only get the score grouping for those searches that don't have any of the search terms in them?

Upvotes: 0

Views: 326

Answers (1)

Blakes Seven
Blakes Seven

Reputation: 50406

You removed the content when you did the $project, and that is why when you go to reference a field later that therefore no longer exists is why it does not show up.

The things to remember with aggregation here is that it is a "pipeline", where the nearest analogy is the pipe | operator which will be available to your shell or command prompt, depending on the type of operating system you are using.

Think in terms of a common Unix idiom:

ps -ef | grep "mongo" | tee "mongoproc".txt

Where there is a ps command and it's output is "piped" to the grep command, which in turn "filters" the content so the output that is "piped" again only contains the matched values when sent to tee, which also redirects output to the named file.

Exactly the same thing happens with the aggregation framework, in that you only get "in" in the following stage what came "out" of the previous one.

So $project works a little differently in the aggregation pipeline than it does within the basic query projection. Here you must specify all of the fields you want "explicitly" in order that these are output and then usable in subsequent stages:

   db.my_collection.aggregate(
      [
        { $match: { 
            $text: { 
                $search: "annual one-time One_time monthly quarterly"
            } 
        }},
        { $project: { 
            _id: 0, 
            amount_in_cents: 1,
            score: { $meta: "textScore" } 
        }},
        { $group: { 
            _id: { score: "$score"},  
           totalAmount: { $sum: { $divide: [ { $add: [ "$amount_in_cents" ] }, 100 ] } },       
           count: { $sum: 1 }
        }}
    ]);

Upvotes: 1

Related Questions