Mongodb query to group by multiple fields and filter

Question

I want to be able to group each "Place" to show over time, how many "PatientIds" they are seeing on a given day and then be able to filter this by what the action is.

Basically Total Patients on y-axis, Date on x-axis and then a filter or stacked chart to show the action. I also thought about a mapreduce, but have never done that in mongo

I can't figure out the correct mongo query. Right now I have:

db.collection.aggregate({"$group":{_id:{place:"$place",date:"$date",action:"$action",count:{$sum:1}}},{$sort:{"_id.date":1,"_id.place":1}})

However, this is just listing out the data. I tried to do a match on all places, but that didn't give me the results I was looking for either. Any ideas?

Example json:

{
    "_id" : ObjectId(""),
    "patientId" : "100",
    "place" : "1",
    "action" : "DIAGNOSED",
    "date" : ISODate("2017-01-20")
}
{
    "_id" : ObjectId(""),
    "patientId" : "101",
    "place" : "1",
    "action" : "PATIENT IN",
    "date" : ISODate("2017-01-20)
}
{
    "_id" : ObjectId(""),
    "patientId" : "200",
    "place" : "2",
    "action" : "MEDICINE",
    "date" : ISODate("2017-01-05")
}
{
    "_id" : ObjectId(""),
    "patientId" : "300",
    "place" : "2",
    "action" : "DIAGNOSED",
    "date" : ISODate("2017-01-31")
}

EDIT - mapreduce

> var map = function(){emit(this.place,1)}
> var reduce = function(key,values){var res = 0;values.forEach(function(v){res+=1});return{count:res};}
> db.new.mapReduce(map,reduce,{out:"mapped_places"});
{
    "result" : "mapped_places",
    "timeMillis" : 88,
    "counts" : {
        "input" : 4,
        "emit" : 4,
        "reduce" : 2,
        "output" : 2
    },
    "ok" : 1
}
> db.mapped_offices.find({})
{ "_id" : "1", "value" : { "count" : 2 } }
{ "_id" : "2", "value" : { "count" : 2 } }
>

Not entirely sure what the problem is but your query has syntax error. Should be db.collection.aggregate({"$group":{_id:{date:"$date",place:"$place",action:"$action"},count:{$sum:1}}},{$sort:{"_id.date":1,"_id.place":1}}). Please consider adding expected output to the post and input if any. — s7vr
– s7vr, Commented Aug 15, 2017 at 15:18
I want to total the number of patients per date per place. So, place:"1", has 2 claims on 2017-01-20, but there are two different actions so when I make the chart I want to be able to view all at once or by a certain action — user6754289
– user6754289, Commented Aug 15, 2017 at 15:51
So you just need to pass the match filter in case of certain action. Something like db.collection.aggregate({$match:{action:"someaction"}}, {"$group":{_id:{date:"$date",place:"‌$place"},count:{$sum:1}‌}},{$sort:{"_id.date‌":1,"_id.place":1}}). For all actions the query I posted in the previous comment should work. — s7vr
– s7vr, Commented Aug 15, 2017 at 15:56
I'm updating the comment with a mapreduce function to see if there is any way to add the date to this. — user6754289
– user6754289, Commented Aug 15, 2017 at 15:58
Also, in what you are saying I need to match all the actions, which I don't know how to do in a $match — user6754289
– user6754289, Commented Aug 15, 2017 at 16:04

s7vr · Accepted Answer · 2017-08-17 02:04:56Z

1

You can try below aggregation query.

db.collection.aggregate([
  {
    "$group": {
      "_id": {
        "date": "$date",
        "place": "$place"
      },
      "actions": {
        "$push": "$action"
      },
      "count": {
        "$sum": 1
      }
    }
  },
  {
    "$unwind": "$actions"
  },
  {
    "$sort": {
      "_id.date": 1,
      "_id.place": 1
    }
  }
]);

This should output something like

{ "_id" : { "date" : ISODate("2017-01-20T00:00:00Z"), "place" : "1"}, "count" : 2, "actions" : "PATIENT IN"  }
{ "_id" : { "date" : ISODate("2017-01-20T00:00:00Z"), "place" : "1"}, "count" : 2, "actions" : "DIAGNOSED"  }

answered Aug 17, 2017 at 2:04

s7vr

76.2k12 gold badges118 silver badges135 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mongodb query to group by multiple fields and filter

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related