Mongodb get count of each categories

Question

i want to get count of each categories. i wrote a query for this purpose but this query returned content document. i want only returned count of each categories content and categories info.

Categories collection:

{
    "_id" : ObjectId("5a6b98864f1408137f79e507"),
    "orderId" : 1,
    "parentId" : ObjectId("5a6b8fbd4f1408137f79e3b3"),
    "title_en" : "Foreign Movie",
    "contentTypes" : 1,
    "isEnabled" : true,
    "state" : 0,
    "expired" : Timestamp(1516998589, 1),
    "created" : Timestamp(1516998589, 2),
    "updated" : Timestamp(1516998589, 3)
},
{
    "_id" : ObjectId("5a6b98864f1408137f79e508"),
    "orderId" : 1,
    "parentId" : ObjectId("5a6b8fbd4f1408137f79e3b3"),
    "title_en" : "Foreign Series",
    "contentTypes" : 1,
    "isEnabled" : true,
    "state" : 0,
    "expired" : Timestamp(1516998589, 1),
    "created" : Timestamp(1516998589, 2),
    "updated" : Timestamp(1516998589, 3)
}

Contents collection:

 {
    "_id" : ObjectId("5a6b8b734f1408137f79e2cc"),
    "categories" : [ 
        {
            "_id" : ObjectId("5a6b98864f1408137f79e507")
        }
    ],
    "status" : 0,
    "created" : Timestamp(1516997542, 4),
    "updated" : Timestamp(1516997542, 5)
}

aggregate query:

db.categories.aggregate([
{$match:{"parentId":{$ne : null}}},
  {$lookup:{from:"contents",localField:"_id",foreignField:"categories._id",as:"_content"}},

        { $group:
            {
                _id:"$_id",
                "data":{"$first":"$$ROOT"}   
            }
        }
    ,{$replaceRoot:{"newRoot":{"$mergeObjects":["$data"]}}},
])

result of above query:

{
    "_id" : ObjectId("5a6b98864f1408137f79e507"),
    "orderId" : 1,
    "parentId" : ObjectId("5a6b8fbd4f1408137f79e3b3"),
    "title_en" : "Foreign Movie",
    "contentTypes" : 1,
    "isEnabled" : true,
    "state" : 0,
    "expired" : Timestamp(1516998589, 1),
    "created" : Timestamp(1516998589, 2),
    "updated" : Timestamp(1516998589, 3),
    "_content" : [ 
        {
        "_id" : ObjectId("5a6b8b734f1408137f79e2cc"),
        "categories" : [ 
            {
                "_id" : ObjectId("5a6b98864f1408137f79e507")
            }
        ],
        "status" : 0,
        "created" : Timestamp(1516997542, 4),
        "updated" : Timestamp(1516997542, 5)
    }
    ]
},
{
    "_id" : ObjectId("5a6b98864f1408137f79e508"),
    "orderId" : 1,
    "parentId" : ObjectId("5a6b8fbd4f1408137f79e3b3"),
    "title_en" : "Foreign Series",
    "contentTypes" : 1,
    "isEnabled" : true,
    "state" : 0,
    "expired" : Timestamp(1516998589, 1),
    "created" : Timestamp(1516998589, 2),
    "updated" : Timestamp(1516998589, 3),
    "_content" : [] /*array content is empty*/
}

as you show it, _content is contains contents documents that returned by $lookup, i have not this result because i want only returned categories info with count of contents with same categories id like below query:

purpose result:

{
    "_id" : ObjectId("5a6b98864f1408137f79e507"),
    "orderId" : 1,
    "parentId" : ObjectId("5a6b8fbd4f1408137f79e3b3"),
    "title_en" : "Foreign Movie",
    "contentTypes" : 1,
    "isEnabled" : true,
    "state" : 0,
    "expired" : Timestamp(1516998589, 1),
    "created" : Timestamp(1516998589, 2),
    "updated" : Timestamp(1516998589, 3),
    "contentCount":1,
    "_content" : [ 
        {
        "_id" : ObjectId("5a6b8b734f1408137f79e2cc")
        }
    ]
}

yes, i want all categories list with count. some categories content is empty, so i need these category result exists in categories list count. for example, in category (Korean Movie) doesn't have any content or category (English Movie) has 10 content. — Ehsan Farahani Asil, Jan 30 '18 at 11:22
Can same category id in category collection match to multiple content category id rows in content collection ? Or is it always one to one relation ? — s7vr, Jan 30 '18 at 12:25

score 0 · Answer 1 · answered Jan 30 '18 at 11:58

You can get to a result very close to the requested one using multiple projections. With the "$project" operator you can move most of the fields around at will, but you need multiple stages in this case.

And yes, you can get your contentCount using this expression:

contentCount: { $sum:{ $size: "$_content" } }

Here is the full query I have used:

db.categories.aggregate([
  {$match: {"parentId":{$ne : null}}},
  {$lookup:{from:"contents",localField:"_id",foreignField:"categories._id",as:"_content"}},
  { $group:
    {
      _id:"$_id",
      "data":{"$first":"$$ROOT"},
      contentCount: { $sum:{ $size: "$_content" } }
    }
  },
  {
     $project: {
       "orderId": "$data.orderId",
       "parentId": "$data.parentId",
       "title_en": "$data.title_en",
       "contentTypes": "$data.contentTypes",
       "isEnabled": "$data.isEnabled",
       "state": "$data.state",
       "expired": "$data.expired",
       "created": "$data.created",
       "updated": "$data.updated",
       "contentCount": "$contentCount",
       "_content": "$data._content"
     }
  },
  {
    $project: {
      "orderId": 1,
      "parentId": 1,
      "parentId": 1,
      "title_en": 1,
      "contentTypes": 1,
      "isEnabled": 1,
      "state": 1,
      "expired": 1,
      "created": 1,
      "updated": 1,
      "contentCount": 1,
      "_content._id" : 1
    }
  }
]).pretty()

This produces on my old MongoDB 3.2 this result with the data you have provided:

{
        "_id" : ObjectId("5a6b98864f1408137f79e508"),
        "contentCount" : 0,
        "orderId" : 1,
        "parentId" : ObjectId("5a6b8fbd4f1408137f79e3b3"),
        "title_en" : "Foreign Series",
        "contentTypes" : 1,
        "isEnabled" : true,
        "state" : 0,
        "expired" : Timestamp(1516998589, 1),
        "created" : Timestamp(1516998589, 2),
        "updated" : Timestamp(1516998589, 3),
        "_content" : [ ]
}
{
        "_id" : ObjectId("5a6b98864f1408137f79e507"),
        "contentCount" : 1,
        "orderId" : 1,
        "parentId" : ObjectId("5a6b8fbd4f1408137f79e3b3"),
        "title_en" : "Foreign Movie",
        "contentTypes" : 1,
        "isEnabled" : true,
        "state" : 0,
        "expired" : Timestamp(1516998589, 1),
        "created" : Timestamp(1516998589, 2),
        "updated" : Timestamp(1516998589, 3),
        "_content" : [
                {
                        "_id" : ObjectId("5a6b8b734f1408137f79e2cc")
                }
        ]
}

thanks. but when run this query mongodb returned error: total size of documents in pipelines mongodb. — Ehsan Farahani Asil, Jan 30 '18 at 12:38
@EhsanFarahaniAsil Is this the complete error message? On the other hand I am pretty sure this should work as I have tried it on MongoDB 3.2. Possibly you are using an older version of MongoDB. This might also be causing the error. — gil.fernandes, Jan 30 '18 at 12:44
@EhsanFarahaniAsil OK, possibly you should now look at this post: https://stackoverflow.com/questions/45724785/aggregate-lookup-total-size-of-documents-in-matching-pipeline-exceeds-maximum-d — gil.fernandes, Jan 30 '18 at 12:46

Mongodb get count of each categories

1 Answers1