MongoDB get total counts of nested documents

Question

I have following document in MongoDB. I want to add a new temporary field as "total_comments" in each document. If the document has no comment then it shows "total_comments": 0 otherwise it shows as "total_comments": n, where n represent a total number of comments in an array of an object. I tried with MongoDB aggregate query, but I am not able to get the desired result.

Schema

    var photoSchema = new Schema({
    user_id: {
        ref: 'User',
        type: mongoose.Schema.Types.ObjectId,
        required: true,
        index: true
    },
    album_id: {
        type: mongoose.Schema.Types.ObjectId,
        ref: 'Album',
        require: false,
        index: true
    },
    photo_name: {
        type: String,
        required: true
    },
    comments: [{
            user_id: {
                type: Schema.Types.ObjectId,
                ref: 'User'
            },
            comment: [],
            like: [{
                    type: Schema.Types.ObjectId,
                    ref: 'User'
                }],
            is_delete: {
                type: Number,
                default: 0,
                index: true
            }, // 0: insert, 1: delete
            created_at: {
                type: Date,
                default: Date.now,
                index: true
            }, // created date
            reply_reference_id: {
                type: Schema.Types.ObjectId
            }
        }],
    is_publish: {
        type: Number,
        default: 0
    },
    is_visible: {
        type: Number,
        default: 0
    },
    is_active: {
        type: Number,
        default: 1
    },
    is_delete: {
        type: Number,
        default: 0
    },
    created_at: {
        type: Date,
        default: Date.now
    },
    updated_at: {
        type: Date
    }
}, {collection: 'photo'});

Current documents

{
    "_id" : ObjectId("58ef5aeb6089260c9efed2d7"),
    "user_id" : ObjectId("57554e7e64c21f560ce002f1"),
    "album_id" : ObjectId("587f6e4e424085443af9e668"),
    "photo_name" : "11425155111184.jpeg",
    "comments":[
        {
            "user_id" : ObjectId("572c80a2220d1d4028437058"),
            "_id" : ObjectId("58edc650acde74110ceec9e2"),           
            "comment" : [{"text" : "comment1"}]
        },
        {
            "user_id" : ObjectId("572c80a2220d1d4028437058"),
            "_id" : ObjectId("58edc650acde74110ceec9e1"),           
            "comment" : [{"text" : "comment2"}]
        },
        {
            "user_id" : ObjectId("572c80a2220d1d4028437058"),
            "_id" : ObjectId("58edc650acde74110ceec9e4"),           
            "comment" : [{"text" : "comment3"}]
        },      
        {
            "user_id" : ObjectId("572c80a2220d1d4028437058"),
            "_id" : ObjectId("58edc650acde74110ceec9e7"),           
            "comment" : [{"text" : "comment4"}]
        },
        {
            "user_id" : ObjectId("572c80a2220d1d4028437058"),
            "_id" : ObjectId("58edc650acde74110ceec9e8"),           
            "comment" : [{"text" : "comment5"}]
        },
    ]
},
{
    "_id" : ObjectId("58ef5aeb6089260c9efed2d6"),
    "user_id" : ObjectId("57554e7e64c21f560ce002f1"),
    "album_id" : ObjectId("587f6e4e424085443af9e668"),
    "photo_name" : "11425155111184.jpeg",
    "comments":[
        {
            "user_id" : ObjectId("572c80a2220d1d4028437028"),
            "_id" : ObjectId("58edc650acde74110ceec9e2"),           
            "comment" : [{"text" : "comment1"}]
        },
        {
            "user_id" : ObjectId("572c80a2220d1d4028437018"),
            "_id" : ObjectId("58edc650acde74110ceec9e1"),           
            "comment" : [{"text" : "comment2"}]
        },
        {
            "user_id" : ObjectId("572c80a2220d1d4028437048"),
            "_id" : ObjectId("58edc650acde74110ceec9e4"),           
            "comment" : [{"text" : "comment3"}]
        },      
        {
            "user_id" : ObjectId("572c80a2220d1d4028437078"),
            "_id" : ObjectId("58edc650acde74110ceec9e7"),           
            "comment" : [{"text" : "comment4"}]
        },
        {
            "user_id" : ObjectId("572c80a2220d1d4028437098"),
            "_id" : ObjectId("58edc650acde74110ceec9e8"),           
            "comment" : [{"text" : "comment5"}]
        },
    ]
},
{
    "_id" : ObjectId("58ef5aeb6089260c9efe72d6"),
    "user_id" : ObjectId("57554e7e64c21f5608e002f1"),
    "album_id" : ObjectId("587f6e4e424085443af9e668"),
    "photo_name" : "11425155111184.jpeg",
    "comments":[
        {
            "user_id" : ObjectId("572c80a2220d1d4028447028"),
            "_id" : ObjectId("58edc650acde74110ceec9e2"),           
            "comment" : [{"text" : "comment1"}]
        },
        {
            "user_id" : ObjectId("572c80a2220d1d4028435018"),
            "_id" : ObjectId("58edc650acde74110ceec9e1"),           
            "comment" : [{"text" : "comment2"}]
        }
    ]
},
{
    "_id" : ObjectId("58ef5aeb60898960c9efe72d6"),
    "user_id" : ObjectId("57554e7e64c21f5608e002f1"),
    "album_id" : ObjectId("587f6e4e424085443af9e668"),
    "photo_name" : "11425155111183.jpeg",
    "comments":[]
},
{
    "_id" : ObjectId("58ef588b6089260c9efe72d6"),
    "user_id" : ObjectId("57554e7e64c21f5608e002f1"),
    "album_id" : ObjectId("587f6e4e424085443af9e668"),
    "photo_name" : "11425155111174.jpeg",
    "comments":[]
}

Expected result

{
    "_id" : ObjectId("58ef5aeb6089260c9efed2d7"),
    "user_id" : ObjectId("57554e7e64c21f560ce002f1"),
    "album_id" : ObjectId("587f6e4e424085443af9e668"),
    "photo_name" : "11425155111184.jpeg",
    "total_comments": 5,
    "comments":[
        {
            "user_id" : ObjectId("572c80a2220d1d4028437058"),
            "_id" : ObjectId("58edc650acde74110ceec9e2"),           
            "comment" : [{"text" : "comment1"}]
        },
        {
            "user_id" : ObjectId("572c80a2220d1d4028437058"),
            "_id" : ObjectId("58edc650acde74110ceec9e1"),           
            "comment" : [{"text" : "comment2"}]
        },
        {
            "user_id" : ObjectId("572c80a2220d1d4028437058"),
            "_id" : ObjectId("58edc650acde74110ceec9e4"),           
            "comment" : [{"text" : "comment3"}]
        },      
        {
            "user_id" : ObjectId("572c80a2220d1d4028437058"),
            "_id" : ObjectId("58edc650acde74110ceec9e7"),           
            "comment" : [{"text" : "comment4"}]
        },
        {
            "user_id" : ObjectId("572c80a2220d1d4028437058"),
            "_id" : ObjectId("58edc650acde74110ceec9e8"),           
            "comment" : [{"text" : "comment5"}]
        },
    ]
},
{
    "_id" : ObjectId("58ef5aeb6089260c9efed2d6"),
    "user_id" : ObjectId("57554e7e64c21f560ce002f1"),
    "album_id" : ObjectId("587f6e4e424085443af9e668"),
    "photo_name" : "11425155111184.jpeg",
    "total_comments": 5,
    "comments":[
        {
            "user_id" : ObjectId("572c80a2220d1d4028437028"),
            "_id" : ObjectId("58edc650acde74110ceec9e2"),           
            "comment" : [{"text" : "comment1"}]
        },
        {
            "user_id" : ObjectId("572c80a2220d1d4028437018"),
            "_id" : ObjectId("58edc650acde74110ceec9e1"),           
            "comment" : [{"text" : "comment2"}]
        },
        {
            "user_id" : ObjectId("572c80a2220d1d4028437048"),
            "_id" : ObjectId("58edc650acde74110ceec9e4"),           
            "comment" : [{"text" : "comment3"}]
        },      
        {
            "user_id" : ObjectId("572c80a2220d1d4028437078"),
            "_id" : ObjectId("58edc650acde74110ceec9e7"),           
            "comment" : [{"text" : "comment4"}]
        },
        {
            "user_id" : ObjectId("572c80a2220d1d4028437098"),
            "_id" : ObjectId("58edc650acde74110ceec9e8"),           
            "comment" : [{"text" : "comment5"}]
        },
    ]
},
{
    "_id" : ObjectId("58ef5aeb6089260c9efe72d6"),
    "user_id" : ObjectId("57554e7e64c21f5608e002f1"),
    "album_id" : ObjectId("587f6e4e424085443af9e668"),
    "photo_name" : "11425155111184.jpeg",
    "total_comments": 2,
    "comments":[
        {
            "user_id" : ObjectId("572c80a2220d1d4028447028"),
            "_id" : ObjectId("58edc650acde74110ceec9e2"),           
            "comment" : [{"text" : "comment1"}]
        },
        {
            "user_id" : ObjectId("572c80a2220d1d4028435018"),
            "_id" : ObjectId("58edc650acde74110ceec9e1"),           
            "comment" : [{"text" : "comment2"}]
        }
    ]
},
{
    "_id" : ObjectId("58ef5aeb60898960c9efe72d6"),
    "user_id" : ObjectId("57554e7e64c21f5608e002f1"),
    "album_id" : ObjectId("587f6e4e424085443af9e668"),
    "photo_name" : "11425155111183.jpeg",
    "total_comments": 0,
    "comments":[]
},
{
    "_id" : ObjectId("58ef588b6089260c9efe72d6"),
    "user_id" : ObjectId("57554e7e64c21f5608e002f1"),
    "album_id" : ObjectId("587f6e4e424085443af9e668"),
    "photo_name" : "11425155111174.jpeg",
    "total_comments": 0,
    "comments":[]
}

When I execute aggregate query it throws me following error everytime.

assert: command failed: {
    "ok" : 0,
    "errmsg" : "The argument to $size must be an Array, but was of type: EOO",
    "code" : 17124
} : aggregate failed
Error: command failed: {
    "ok" : 0,
    "errmsg" : "The argument to $size must be an Array, but was of type: EOO",
    "code" : 17124
} : aggregate failed
    at Error (<anonymous>)
    at doassert (src/mongo/shell/assert.js:11:14)
    at Function.assert.commandWorked (src/mongo/shell/assert.js:244:5)
    at DBCollection.aggregate (src/mongo/shell/collection.js:1149:12)
    at (shell):1:10
2017-04-25T11:07:31.198+0530 Error: command failed: {
    "ok" : 0,
    "errmsg" : "The argument to $size must be an Array, but was of type: EOO",
    "code" : 17124
} : aggregate failed at src/mongo/shell/assert.js:13

The same types of schema I used in other collections, but they works fine but I don't know why this is not working in this collections.

Try `db.collection.aggregate({ $addFields: { "total_comments": { $size: "$comments" } } })` — s7vr, Apr 24 '17 at 14:54
Possible duplicate of [MongoDB: count the number of items in an array](http://stackoverflow.com/questions/21387969/mongodb-count-the-number-of-items-in-an-array) — s7vr, Apr 24 '17 at 14:55
@Veeram : I tried a some other way also but I did not able to resolve an issue that is why I posted here. — Dipak, Apr 25 '17 at 05:34
@Veeram: I have update a question with error which throws when I execute query. Please refer. any of the things does not help me. — Dipak, Apr 25 '17 at 05:41
Looks like you have `comments` which is not an array. Can you verify your data ? — s7vr, Apr 25 '17 at 05:54
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/142568/discussion-between-dipak-chavda-and-veeram). — Dipak, Apr 25 '17 at 06:26

score 0 · Answer 1 · answered Oct 25 '18 at 12:48

You can use $project with $size:

db.collectionName.aggregate([
    {
        $project: {
            user_id: 1,
            album_id: 1,
            photo_name: 1,
            total_comments: { $size: "$comments" },
            comments: 1
        }
    }
])

MongoDB get total counts of nested documents

1 Answers1