CODEWITHSUNDEEP
node.jsmongodbmongoosemongodb-queryaggregation-framework
Adi Sparta
Adi Sparta

Reputation: 525

Mongoose using subquery in aggregation to sum data

I am new in mongoose. I have model like this. 

var ForumSchema = new mongoose.Schema({
User_id             : {type : Schema.Types.ObjectId, ref : 'User'},
Title               : {type : String},
Content             : {type : String},
Tags                : [{type : String}],
isPublic            : {type : Boolean, default : false},
Vote                : [{
    User_id         : {type : Schema.Types.ObjectId, ref : 'User'},
    Kind            : {type : Boolean}
}],
Rate                : [{
    User_id         : {type : Schema.Types.ObjectId, ref : 'User'},
    Count           : {type : Number}
}],
Comment             : [{
    User_id         : {type : Schema.Types.ObjectId, ref : 'User'},
    Content         : String,
    Created_at      : {type : Date, required : true, default : Date.now}
}],
Created_at          : {type : Date, required : true, default : Date.now},
Updated_at          : {type : Date}

});

I want to get Forum data with sum of Vote that value is true. Like this json.

{
[
    _id         : <Some object id>,
    User_id     : <Some object id from User Model>,
    Title       : <Title>,
    Content     : <Content>,
    Tags        : [Some array],
    isPublic    : true,
    UpVote      : 23,
    ....
    ....
    ....
]

}

In mysql I can do this by using subquery. How can I do it in mongoose?

Upvotes: 1

Views: 1922

Answers (2)

chridam
chridam

Reputation: 103345

With MongoDB server 3.4 and above, you can run an aggregate pipeline that uses the $addFields operator which has a $filter on the Vote array to filter those element that have a Kind property value matching true and when you get the filtered array, use it as an input expression for the $size operator which then calculates the count of the items in the filtered array.

Consider the following operation to get the desired result:

Forum.aggregate([
    {
        "$addFields": {
            "UpVote": {
                "$size": {
                    "$filter": {
                        "input": "$Vote",
                        "as": "el",
                        "cond": "$$el.Kind"
                    }
                }
            }
        }
    }
]).exec((err, results) => {
    if (err) throw err;
    console.log(results);
})

Explanations

In the above, the inner expression 

{
    "$filter": {
        "input": "$Vote",
        "as": "el",
        "cond": "$$el.Kind"
    }
}

selects a subset of the array to return based on the specified condition. As a result, it returns an array with only those elements that match the condition.

The input property refers to an expression that resolves to an array. In the above, the input is the Votes array.

The other field as represents the variable name for the element in the input array. The as expression accesses each element in the input array by this variable.

The cond field holds an expression that determines whether to include the element in the resulting array. The expression accesses the element by the variable name specified in as.

So in the above if the element in the array being evaluated has the Kind subproperty equal to true, denoted by the expression "$$el.Kind", then the condition is matched and the element is included in the subset to be returned.

As a simple example, take for instance this high-level expression:

{
    "$filter": {
        "input": [
            { "Kind": true, "User_id": "58afed97bc343887a9ac9206" },
            { "Kind": false, "User_id": "58ad50a429b2961777f91c97" },
            { "Kind": true, "User_id": "58b3f0f598501abacd8ff391" }
        ],
        "as": "el",
        "cond": {
            "$eq": ["$$el.Kind", true]
        }
    }
}

returns the array

[
    { "Kind": true, "User_id": "58afed97bc343887a9ac9206" },
    { "Kind": true, "User_id": "58b3f0f598501abacd8ff391" }
]

The conditional part

"cond": {
    "$eq": ["$$el.Kind", true]
}

can be simplified to just

"cond": "$$el.Kind"

as "$$el.Kind" expression already evaluates to a boolean.

The $size operator trivially calculates the number of elements in an array, thus the expression, for example

{
    "$size":    {
        "$filter": {
            "input": [
                { "Kind": true, "User_id": "58afed97bc343887a9ac9206" },
                { "Kind": false, "User_id": "58ad50a429b2961777f91c97" },
                { "Kind": true, "User_id": "58b3f0f598501abacd8ff391" }
            ],
            "as": "el",
            "cond": {
                "$eq": ["$$el.Kind", true]
            }
        }
    }
}

is expressed as

{
    "$size": [
        { "Kind": true, "User_id": "58afed97bc343887a9ac9206" },
        { "Kind": true, "User_id": "58b3f0f598501abacd8ff391" }
    ]
}

and return a result with 2 as count.

For the contrary DownVote count, the same logic applies:

Forum.aggregate([
    {
        "$addFields": {
            "UpVote": {
                "$size": {
                    "$filter": {
                        "input": "$Vote",
                        "as": "el",
                        "cond": "$$el.Kind"
                    }
                }
            },
            "DownVote": {
                "$size": {
                    "$filter": {
                        "input": "$Vote",
                        "as": "el",
                        "cond": { "$not": ["$$el.Kind"] }
                    }
                }
            }
        }
    }
]).exec((err, results) => {
    if (err) throw err;
    console.log(results);
})

For the earlier MongoDB version 3.2, you will need to project each and every other element in the document:

Forum.aggregate([
    {
        "$project": {
            "User_id"     : 1,
            "Title"       : 1,
            "Content"     : 1,
            "Tags"        : 1,
            "isPublic"    : 1,
            "UpVote"      : {
                "$size": {
                    "$filter": {
                        "input": "$Vote",
                        "as": "el",
                        "cond": "$$el.Kind"
                    }
                }
            },
            ....
            ....
            ....
        }
    }
]).exec((err, results) => {
    if (err) throw err;
    console.log(results);
})

For versions that do not support the $filter operator, use the $setDifference operator instead as:

Forum.aggregate([
    {
        "$project": {
            "User_id"     : 1,
            "Title"       : 1,
            "Content"     : 1,
            "Tags"        : 1,
            "isPublic"    : 1,
            "UpVote"      : {
                "$size": {
                    "$setDifference": [
                        { "$map": {
                            "input": "$Vote",
                            "as": "el",
                            "in": { "$cond": ["$$el.Kind", "$$el", false] }               
                        }},
                        [false]
                    ]
                }
            },
            ....
            ....
            ....
        }
    }
]).exec((err, results) => {
    if (err) throw err;
    console.log(results);
})

Upvotes: 5

karthick Ramanathan
karthick Ramanathan

Reputation: 910

you can use aggregate in mongodb

db.getCollection('forum').aggregate([{
        $unwind: "$Vote"
    },
    {
        $match: {
            "Vote. Kind": true
        }
    },
    {
        $group: {
            _id: '$_id',
            "User_id": {
                '$first': '$User_id'
            },
            "Title": {
                '$first': '$Title'
            },
            UpVote: {
                $sum: 1
            }
        }
    }
])

you can use this same query in mongoose also, finally you get number of up votes

Upvotes: 0

Related Questions