MongoDB aggregate query for values in an array

Question

So I have data that looks like this:

{
   _id: 1, 
   ranking: 5, 
   tags: ['Good service', 'Clean room']
}

Each of these stand for a review. There can be multiple reviews with a ranking of 5. The tags field can be filled with up to 4 different tags.

4 tags are: 'Good service', 'Good food', 'Clean room', 'Need improvement'

I want to make a MongoDB aggregate query where I say 'for each ranking (1-5) give me the number of times each tag occurred for each ranking.

So an example result might look like this, _id being the ranking:

 [ 
   { _id: 5, 
    totalCount: 5, 
    tags: {
       goodService: 1, 
       goodFood: 3, 
       cleanRoom: 1, 
       needImprovement: 0
   }, 
   { _id: 4, 
    totalCount: 7, 
    tags: {
       goodService: 0, 
       goodFood: 2, 
       cleanRoom: 3, 
       needImprovement: 0
   }, 
   ...
]

Having trouble with the counting the occurrences of each tag. Any help would be appreciated

s7vr · Accepted Answer · 2018-08-21 16:43:40Z

2

You can try below aggregation.

db.colname.aggregate([
  {"$unwind":"$tags"},
  {"$group":{
    "_id":{
      "ranking":"$ranking",
      "tags":"$tags"
    },
    "count":{"$sum":1}
  }},
  {"$group":{
    "_id":"$_id.ranking",
    "totalCount":{"$sum":"$count"},
    "tags":{"$push":{"tags":"$_id.tags","count":"$count"}}
  }}
])

To get the key value pair instead of array you can replace $push with $mergeObjects from 3.6 version.

"tags":{"$mergeObjects":{"$arrayToObject":[[["$_id.tags","$count"]]]}}

edited Aug 21, 2018 at 16:43

answered Aug 21, 2018 at 16:29

s7vr

76.2k12 gold badges118 silver badges135 bronze badges

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

6 Comments

J Seabolt Over a year ago

So this works, except it's only bringing back the first of the rankings. I get the ranking for 1. The data looks reasonable. But I don't get info for 2,3,4 or 5.

J Seabolt Over a year ago

Some of the reviews don't have tags. I might not have mentioned that

s7vr Over a year ago

That is alright. I was able to get all rankings when I ran the query with multiple documents. Use {"$unwind":{path:"$tags", preservenullandemptyarrays:true}} to account for missing tags.

J Seabolt Over a year ago

So almost there, the data is coming in. But I am also getting this error: "TypeError: Cannot read property 'tags' of undefined" I imagine this has to do with including the reviews with null ratgins. Can we get rid of that?

J Seabolt Over a year ago

Giving you a point for the help so far. Thanks for sticking with me. I really appreciate it.

|

Collectives™ on Stack Overflow

MongoDB aggregate query for values in an array

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related