Mongoose aggregation "$ sum" of lines in the sub

Question

I am pretty good at sql queries, but I can't seem to have my head around grouping and getting the sum of mongo db documents,

With this in mind , I have a model of working with a circuit, as shown below:

    {
        name: {
            type: String,
            required: true
        },
        info: String,
        active: {
            type: Boolean,
            default: true
        },
        all_service: [

            price: {
                type: Number,
                min: 0,
                required: true
            },
            all_sub_item: [{
                name: String,
                price:{ // << -- this is the price I want to calculate
                    type: Number,
                    min: 0
                },
                owner: {
                    user_id: {  //  <<-- here is the filter I want to put
                        type: Schema.Types.ObjectId,
                        required: true
                    },
                    name: String,
                    ...
                }
            }]

        ],
        date_create: {
            type: Date,
            default : Date.now
        },
        date_update: {
            type: Date,
            default : Date.now
        }
    }

I would like to have the sum of the column pricewhere present owner, I tried below, but no luck

 Job.aggregate(
        [
            {
                $group: {
                    _id: {}, // not sure what to put here
                    amount: { $sum: '$all_service.all_sub_item.price' }
                },
                $match: {'not sure how to limit the user': given_user_id}
            }
        ],
        //{ $project: { _id: 1, expense: 1 }}, // you can only project fields from 'group'
        function(err, summary) {
            console.log(err);
            console.log(summary);
        }
    );

Can anyone lead me in the right direction thanks in advance

Answer 1

Primer

---

, "", "" | Unix . "" "" ..

, , , "" , , , .

all_service . , "" , "", "all_sub_item". , , "all_sub_item" , .

"" SQL, " ". " " , . .

, "" , "-" , SQL, "" "". "" - , .

, . , "" " " , "". " " :

{
    "a": 1,
    "b": [
        { 
            "c": 1,
            "d": [
                { "e": 1 }, { "e": 2 }
            ]
        },
        { 
            "c": 2,
            "d": [
                { "e": 1 }, { "e": 2 }
            ]
        }
    ]
}

:

{ "a" : 1, "b" : { "c" : 1, "d" : { "e" : 1 } } }
{ "a" : 1, "b" : { "c" : 1, "d" : { "e" : 2 } } }
{ "a" : 1, "b" : { "c" : 2, "d" : { "e" : 1 } } }
{ "a" : 1, "b" : { "c" : 2, "d" : { "e" : 2 } } }

- $unwind, , $unwind :

db.collection.aggregate([
    { "$unwind": "$b" },
    { "$unwind": "$b.d" }
])

, "pipe" "$ b" :

{ "a" : 1, "b" : { "c" : 1, "d" : [ { "e" : 1 }, { "e" : 2 } ] } }
{ "a" : 1, "b" : { "c" : 2, "d" : [ { "e" : 1 }, { "e" : 2 } ] } }

, "$ b.d", " - ". .

---

"" "" , , "" , . , , $unwind, , .

, "user_id" . , , , .

, "" , , . "", " " - . , "" , , "-", actualy "" .

, "" $match "", $match , "" $unwind, , .

Job.aggregate(
    [
        // Match to filter possible "documents"
        { "$match": { 
            "all_service.all_sub_item.owner": given_user_id
        }},

        // De-normalize arrays
        { "$unwind": "$all_service" },
        { "$unwind": "$all_service.all_subitem" },

        // Match again to filter the array elements
        { "$match": { 
            "all_service.all_sub_item.owner": given_user_id
        }},

        // Group on the "_id" for the "key" you want, or "null" for all
        { "$group": {
            "_id": null,
            "total": { "$sum": "$all_service.all_sub_item.price" }
        }}

    ],
    function(err,results) {

    }
)

, MongoDB 2.6 $redact. " " $unwind:

Job.aggregate(
    [
        // Match to filter possible "documents"
        { "$match": { 
            "all_service.all_sub_item.owner": given_user_id
        }},

        // Filter arrays for matches in document
        { "$redact": {
            "$cond": {
                "if": { 
                    "$eq": [ 
                        { "$ifNull": [ "$owner", given_user_id ] },
                        given_user_id
                    ]
                },
                "then": "$$DESCEND",
                "else": "$$PRUNE"
            }
        }},

        // De-normalize arrays
        { "$unwind": "$all_service" },
        { "$unwind": "$all_service.all_subitem" },

        // Group on the "_id" for the "key" you want, or "null" for all
        { "$group": {
            "_id": null,
            "total": { "$sum": "$all_service.all_sub_item.price" }
        }}

    ],
    function(err,results) {

    }
)

"" , "" $unwind. , , , "". "" , - "" , "". $match , :

Job.aggregate(
    [
        // Match to filter possible "documents"
        { "$match": { 
            "all_service.all_sub_item.owner": given_user_id
        }},

        // Filter arrays for matches in document
        { "$project": {
            "all_items": {
              "$setDifference": [
                { "$map": {
                  "input": "$all_service",
                  "as": "A",
                  "in": {
                    "$setDifference": [
                      { "$map": {
                        "input": "$$A.all_sub_item",
                        "as": "B",
                        "in": {
                          "$cond": {
                            "if": { "$eq": [ "$$B.owner", given_user_id ] },
                            "then": "$$B",
                            "else": false
                          }
                        }
                      }},
                      false
                    ]          
                  }
                }},
                [[]]
              ]
            }
        }},


        // De-normalize the "two" level array. "Double" $unwind
        { "$unwind": "$all_items" },
        { "$unwind": "$all_items" },

        // Group on the "_id" for the "key" you want, or "null" for all
        { "$group": {
            "_id": null,
            "total": { "$sum": "$all_items.price" }
        }}

    ],
    function(err,results) {

    }
)

"" $redact. $map "in". "" $map "" .

$cond, , "", inner "array elment, false.

$setDifference false. "" "" , false, "", . , "" , :

[[{ "_id": 1, "price": 1, "owner": "b" },{..}],[{..},{..}]]

"" _id mongoose ( , ), "" "set", .

$unwind "", , .

, , . , "" , "-" .

Answer 2

, , SQL-, "sum (prices) WHERE owner IS NOT NULL".

$match, . , -

$match: { all_service.all_sub_items.owner : { $exists: true } }

, .

, , . - . , . . $unwind.

$unwind: { "$all_service.all_sub_items" }

, , . $group. $group . :

_id: {}, // not sure what to put here

, , . , _id , "GROUP BY" sql. $sum , , _id. , " " , $unwind, $group. $sum .

, , , $sum .

$group : { _id : $_id, totalAmount : { $sum : '$all_service.all_sub_item.price' } }

_id, , .

, node. , , $match , $unwind , . !