Mongo nested aggregate statement-CodePudding

I'm having trouble with aggregation in Mongo. Currently, the JSON format looks like below:

{
  "_id": {
    "$oid": "63074885ff3acbe0d63d7687"
  },
  "iso_code": "AFG",
  "country": "Afghanistan",
  "year": "1900",
  "population": 100000
  "emissions": 100
}

With country, year, and population. What I want to do is to find the emissions of the countries that have a population within 0.9 to 1.1* of "country": "Singapore" population. What I have currently done is this with the aggregation framework:

[{
 $match: {
  $and: [
   {
    country: 'Singapore'
   },
   {
    year: {
     $gte: '2010'
    }
   },
   {
    year: {
     $lte: '2020'
    }
   }
  ]
 }
}, {
 $group: {
  _id: {
   grpcountry: '$country'
  },
  avgpopu: {
   $avg: {
    $convert: {
     input: '$population',
     to: 'decimal',
     one rror: 0,
     onNull: 0
    }
   }
  }
 }
}, {
 $addFields: {
  popurange: [
   {
    $multiply: [
     0.9,
     '$avgpopu'
    ]
   },
   {
    $multiply: [
     1.1,
     '$avgpopu'
    ]
   }
  ]
 }
}]

What this gives me is an array of the range of the population of countries I want from my data, which is between 0.9* to 1.1* of Singapore's population. However, I am unsure how to proceed from here, because I need to use this array to find the countries with population that fall between these two array values and retrieve the emissions for 2010-2020 of that country for visualising. I'm legitimately stuck here and any advice is appreciated. Thanks!

These are the current results from my aggregation:

{
  "_id": {
    "grpcountry": "Singapore"
  },
  "avgpopu": {
    "$numberDecimal": "5555421.636363636363636363636363636"
  },
  "popurange": [
    {
      "$numberDecimal": "4999879.472727272727272727272727272"
    },
    {
      "$numberDecimal": "6110963.800000000000000000000000000"
    }
  ]
}

CodePudding user response：

Try this one:

db.collection.aggregate([
   {
      $facet: {
         singapore: [
            { $match: { country: "Singapore" } },
            // set the population boundaries
            {
               $addFields: {
                  popurange: [
                     { $multiply: [0.9, '$population'] },
                     { $multiply: [1.1, '$population'] }
                  ]
               }
            }
         ],
         others: [],
      }
   },
   { $unwind: "$others" },
   { // Filter by year
      $set: {
         singapore: {
            $first: {                
               $filter: {
                  input: "$singapore",
                  cond: { $eq: ["$$this.year", "$others.year"] }
               }
            }
         }
      }
   },
   { // Filter by population range
      $match: {
         $expr: {
            $and: [
               { $gte: ["$others.population", { $first: "$singapore.popurange" }] },
               { $lte: ["$otehrs.population", { $last: "$singapore.popurange" }] },
            ]
         }
      }
   },
   // Some cosmetics
   { $replaceWith: "$others" },
   { $match: { country: { $ne: "Singapore" } } }
])