CODEWITHSUNDEEP
mongodbaggregate
cmarrero01
cmarrero01

Reputation: 711

Count articles grouping by tags mongodb

I had a lot of articles with a field called tags, and is an array of tags _ids, and for statistics purpose I want to count how many articles we had by each tag. If tags were a simple tag _id, it's easy because I could group by tag, but is an array of tags, and I can't group by that field.

First I try with this:

db.note.aggregate([{$match: {
  publishedAt: {
    $gte: ISODate('2018-01-01'),
    $lte: ISODate('2019-01-01')
  }
}}, {$group: {
  _id: "$tags",
  "total": {
    "$sum": 1
  }
}}, {$lookup: {
  from: 'tags',
  localField: '_id',
  foreignField: '_id',
  as: 'tag'
}}, {$unwind: {
  path: "$tag"
}}, {$project: {
  total: 1,
  "tag.name": 1
}}, {$sort: {
  total: -1
}}])

But that doesn't work, that query, group by tags group, so I try to do this:

{
                '$match': {
                  'publishedAt': {
                    '$gte': new Date(req.body.gte), 
                    '$lte': new Date(req.body.lte)
                  }
                }
              },
              {
                '$unwind': {
                  'path': '$tags'
                }
              }, {
                '$group': {
                  '_id': '$tags', 
                  'total': {
                    '$sum': 1
                  }
                }
              }, {
                '$lookup': {
                  'from': 'tags', 
                  'localField': '_id', 
                  'foreignField': '_id', 
                  'as': 'tag'
                }
              }, {
                '$project': {
                  'total': 1, 
                  'tag.name': 1
                }
              }, {
                '$sort': {
                  'total': -1
                }
              },
              {
                '$unwind': {
                  'path': '$tag'
                }
              }
            )

But the problem with this, that group for the first tag from the array and I miss all other tags in that array.

What do you think will be the solution?

Upvotes: 1

Views: 521

Answers (1)

prasad_
prasad_

Reputation: 14317

I had a lot of articles with a field called tags, and is an array of tags _ids, and for statistics purpose I want to count how many articles we had by each tag.

You can try this (I am assuming the following input documents):

notes:

{ _id: 1, name: "art-1", author: "ab", tags: [ "t1", "t2" ] },
{ _id: 2, name: "art-2", author: "cd", tags: [ "t1", "t3" ] },
{ _id: 3, name: "art-3", author: "wx", tags: [ "t4", "t3" ] },
{ _id: 4, name: "art-4", author: "yx", tags: [ "t1" ] }

tags:

{ _id: 1, id: "t1", name: "t1's name" },
{ _id: 2, id: "t2", name: "t2's name" },
{ _id: 3, id: "t3", name: "t3's name" },
{ _id: 4, id: "t4", name: "t4's name" }


The Query:

db.tags.aggregate( [
{
  $lookup: {
    from: "notes",
    localField: "id",
    foreignField: "tags",
    as: "tag_matches"
  }
},
{ $project: {  id: 1, name: 1, _id: 0, count: { $size: "$tag_matches" } } }
] )

The Output:

{ "id" : "t1", "name" : "t1's name", "count" : 3 }
{ "id" : "t2", "name" : "t2's name", "count" : 1 }
{ "id" : "t3", "name" : "t3's name", "count" : 2 }
{ "id" : "t4", "name" : "t4's name", "count" : 1 }

Upvotes: 2

Related Questions