Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MongoDB aggregation - project field values as fields

Having some trouble getting my data into a tricky format.

Documents have these fields: subject, importance [high,medium,low]

sample document:

{_id: "", subject: "red", importance: "high"}

I like to return data that looks like:

[{_id: subject, high: 5, medium: 6, low: 3}] 

The numbers correspond with the number of documents with each importance level

This is what I have so far:

{
$group: {
  _id: {subject: "$subject", importance: "$importance"},
  count: {$sum: 1},
  }
 },
 {
   $group: {
     _id: "$_id.subject",
     data: {
       $push: {
          importance: "$_id.importance", count: "$count"
       }
     }
   }
 }

Is there a way that I can use the value of $_id.importance and make that the key and have $count as the value?

like image 992
GJL12 Avatar asked Dec 20 '15 02:12

GJL12


People also ask

How do you value a field in MongoDB?

You can extract the value of a field by appending that field's name to your query when using findOne() .

How do I capture a specific field in MongoDB?

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

How do I add a field in MongoDB aggregate?

You can include one or more $addFields stages in an aggregation operation. To add field or fields to embedded documents (including documents in arrays) use the dot notation. See example. To add an element to an existing array field with $addFields , use with $concatArrays .

Which field is always included in a projection unless specifically excluded?

The _id field is included automatically unless specifically excluded.


1 Answers

You could aggregate as below:

  • $group by subject and importance, get the respective counts.
  • Then comes the tricky part, the conditional $project, it would grow linearly with respect to the number of options the importance field could hold. currently it is three - high, low and medium.
  • $group the result back again by subject and use the $sum operator to accumulate the counts for the different values of the importance field.

sample code:

db.t.aggregate([
{$group:{"_id":{"subject":"$subject",
                "importance":"$importance"},
         "count":{$sum:1}}},
{$project:{"_id":0,
           "subject":"$_id.subject",
           "result":{$cond:[
                           {$eq:["$_id.importance","high"]},
                           {"high":"$count"},
                           {$cond:[{$eq:["$_id.importance","low"]},
                                   {"low":"$count"},
                                   {"medium":"$count"}]}]}}},
{$group:{"_id":"$subject",
         "low":{$sum:"$result.low"},
         "medium":{$sum:"$result.medium"},
         "high":{$sum:"$result.high"}}},
])

test data:

db.t.insert([
{"subject":"history","importance":"high"},
{"subject":"geography","importance":"low"},
{"subject":"history","importance":"low"},
{"subject":"history","importance":"medium"},
{"subject":"geography","importance":"low"},
{"subject":"history","importance":"low"}
])

result:

{ "_id" : "geography", "low" : 2, "medium" : 0, "high" : 0 }
{ "_id" : "history", "low" : 2, "medium" : 1, "high" : 1 }
like image 162
BatScream Avatar answered Sep 30 '22 10:09

BatScream