0

I have ListPrice field in collection on that price Ii have to calculate min, max, median, avg of all data, active standardStatus , sold standardStatus.

I have tried to calculate using aggregation and for loop but it won't work 

        db.collection('selected_properties').aggregate([
        { presentation_id : ObjectId(req.body.presentation_id),
          checked_status : true}
        },
        {
            $lookup : { from :'properties', localField : 'property_id', foreignField : '_id', as : 'property_info'}
        },
        {
            $unwind : {path : '$property_info', preserveNullAndEmptyArrays : true}
        },

        {
            $sort : {'property_info.ListPrice' : 1}
        },
        {
            $group:{
                 _id: "$user_id",
               minActiveListPrice: { $min: { $cond: [ {                 
                       $eq: [ "$property_info.StandardStatus", "A" ]}, 
                              '$property_info.ListPrice',''  ] } },
               maxActiveListPrice: { $max: { $cond: [ {
                        $eq: [ "$property_info.StandardStatus", "A" ]}, 
                               '$property_info.ListPrice',0 ] } },
               avgActiveListPrice: { $avg: { $cond: [ {
                        $eq: [ "$property_info.StandardStatus", "A" ]},
                                '$property_info.ListPrice',''  ] } },
               medianActiveListprice: { $push: { $cond: [ {
                        $eq: [ "$property_info.StandardStatus", "A" ]},                        
                               '$property_info.ListPrice',''  ] } },                                        
               minsoldListPrice: { $min: { $cond: [ {
                        $eq: [ "$property_info.StandardStatus", "S" ]},
                              '$property_info.ListPrice',''  ] } },
               maxsoldListPrice: { $max: { $cond: [ {
                        $eq: [ "$property_info.StandardStatus", "S" ]},
                              '$property_info.ListPrice',0  ] } },
               avgsoldListPrice: { $avg: { $cond: [ {
                        $eq: [ "$property_info.StandardStatus", "S" ]},
                              '$property_info.ListPrice',''  ] } },

                 avgPrice: { $avg: "$property_info.ListPrice" },
                 maxPrice: { $max: "$property_info.ListPrice" },
                 minPrice: { $min: "$property_info.ListPrice" },
              }
               median: { $push:  "$property_info.ListPrice"}                         
            }
         },
Bharti Rawat
  • 1,949
  • 21
  • 32
Deeksha gupta
  • 659
  • 6
  • 15
  • 1
    I suggest you to have a look at the thorough MongoDB documentation: https://docs.mongodb.com/manual/aggregation/. I'm sure you can figure this out on your own. – Lucas Jun 25 '19 at 06:26
  • For median, check [this question](https://stackoverflow.com/questions/20456095/calculate-the-median-in-mongodb-aggregation-framework/76972263#76972263) – nimrod serok Aug 24 '23 at 19:07

1 Answers1

0
db.collection('selected_properties').aggregate([
        {
            $match : { presentation_id : ObjectId(req.body.presentation_id),
                      checked_status : true}
        },
        {
            $lookup : { from :'properties', localField : 'property_id', 
                        foreignField : '_id', as : 'property_info'}
        },
        {
            $unwind : {path : '$property_info', preserveNullAndEmptyArrays : true}
        },

        {
            $sort : {'property_info.ListPrice' : 1}
        },
        {
            $group:
              {
                _id: "$user_id",
                minActiveListPrice: { $min: { $cond: [ {                 
                         $eq: [ "$property_info.StandardStatus", "A" ]}, 
                                '$property_info.ListPrice',''  ] } },
                maxActiveListPrice: { $max: { $cond: [ {
                         $eq: [ "$property_info.StandardStatus", "A" ]}, 
                                '$property_info.ListPrice',0 ] } },
                avgActiveListPrice: { $avg: { $cond: [ {
                         $eq: [ "$property_info.StandardStatus", "A" ]},
                                '$property_info.ListPrice',''  ] } },
                medianActiveListprice: { $push: { $cond: [ {
                         $eq: [ "$property_info.StandardStatus", "A" ]},
                                '$property_info.ListPrice',null ] } },

                avgPrice: { $avg: "$property_info.ListPrice" },
                maxPrice: { $max: "$property_info.ListPrice" },
                minPrice: { $min: "$property_info.ListPrice" },
                median: { $push:  "$property_info.ListPrice"}                         
            }
        },
        { "$project": {
            "minActiveListPrice":1,
            "maxActiveListPrice":1,
            "avgActiveListPrice":1, 
            "avgPrice": 1,
            "maxPrice": 1,
            "minPrice": 1,
            "medianActiveListpricevalue": {
                $let: {
                    vars: {
                       arr: { $filter: {
                                input: "$medianActiveListprice",
                                as: "aa",
                                cond: {$ne:["$$aa",null]}
                            }},

                    },
                    in: {  "$cond": {
                        "if": {
                            "$eq": [{$mod: [ {$size:"$$arr"}, 2 ]}, 0]
                        },
                        "then": {
                            $avg:[ 
                            { $arrayElemAt: [ "$$arr", {$subtract:[{$divide: [ {$size:"$$arr"}, 2 ]},1]}]},
                            { $arrayElemAt: [ "$$arr", {$divide: [ {$size:"$$arr"}, 2 ]}]}
                            ]
                        },
                        "else": {
                            $arrayElemAt: [ "$$arr",{$floor : {$divide: [ {$size:"$$arr"}, 2 ]}}]
                       }
                            }}
                 }
            },

            "medianvalue":{  "$cond": {
                "if": {
                  "$eq": [{$mod: [ {$size:"$median"}, 2 ]}, 0]
                }
                "then": {
                    $avg:[ 
                    { $arrayElemAt: [ "$median", {$subtract:[{$divide: [ {$size:"$median"}, 2 ]},1]}]},
                    { $arrayElemAt: [ "$median", {$divide: [ {$size:"$median"}, 2 ]}]}
                    ]
                },
                "else": {
                     $arrayElemAt: [ "$median",{$floor : {$divide: [ {$size:"$median"}, 2 ]}}]
                }
            }}
        } }
    ])
Deeksha gupta
  • 659
  • 6
  • 15
  • is this an answer to your question? If not please edit your question instead and delete the supplement from answer section. – bummi Jun 25 '19 at 10:09