MongoDB: Group values in nested array to get count

Question

I've searched but could not find an answer to my problem. I've got a collection with this sample structure in MongoDB:

{
  "_id": ObjectID(1),
  "Manual_Tags": [
    {
      _source: {
        tags: [ "tag1", "tag2", "tag3"]
        other_values: ...
      }
    }
  ]
}
{
  "_id": ObjectID(2),
  "Manual_Tags": [
    {
      _source: {
        tags: [ "tag2", "tag3", "tag4"]
        other_values: ...
      }
    }
  ]
}

I need to know the count of instances for each value of "tags" existing in the collection for all documents (Manual_Tags[]._source.tags[]). That is, for the 2 documents in the sample above, the listing would be: tag1: 1 tag2: 2 tag3: 2 tag4: 1

Thanks in advance for any help.

sidgate · Accepted Answer · 2017-09-27 10:32:44Z

3

You need to look at aggregation queries. First you need to unwind both the arrays and then just group on tags to get the count

db.collection.aggregate(
  {$unwind: '$Manual_Tags'},
  {$unwind: '$Manual_Tags._source.tags'},
  {$group: {_id: '$Manual_Tags._source.tags', count:{$sum:1}}}
)

answered Sep 27, 2017 at 10:32

sidgate

15.3k12 gold badges76 silver badges132 bronze badges

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

2 Comments

Alfredo Over a year ago

Thank you. That simple query did it.

rohit13807 Over a year ago

Hello @sidgate, It is working fine, but here I need to add limit with highest count first. Can you please help me out. For an instance, my result is : { "_id" : "Platterz.co", "count" : 1 } { "_id" : "ChowNow", "count" : 3 } { "_id" : "cater2.me", "count" : 1 } { "_id" : "App-App.co", "count" : 1 } { "_id" : "airportsherpa.io", "count" : 1 } { "_id" : "Eat24", "count" : 4 } { "_id" : "Chowbus", "count" : 5 } But I want Like: { "_id" : "Chowbus", "count" : 5 } { "_id" : "Eat24", "count" : 4 } { "_id" : "ChowNow", "count" : 3 }

Chinthaka Fernando · Accepted Answer · 2020-10-06 09:28:10Z

1

Using Projections and Groups

db.mycollection.aggregate(
    [
        {
            $project: {
                _id:0,
                tag_count:{$size:"$Manual_Tags._source.tags"},
            }
        }, 
        {
            $group: {
                tag_total:{$sum:"$tag_count"},
            }
        }
    ]
)

answered Oct 6, 2020 at 9:28

Chinthaka Fernando

82411 silver badges13 bronze badges

Collectives™ on Stack Overflow

MongoDB: Group values in nested array to get count

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related