Find all the countries Birthrate lower than 20, group them by region, find lowest birthrate region-CodePudding

 {
    _id: ObjectId("63a9678da72f51f8dc89dfcf"),
    Country: 'Afghanistan',
    Region: 'ASIA (EX. NEAR EAST)',
    Population: 31056997,
    'Area (sq': { ' mi': { ')': 647500 } },
    'Net migration': '23,06',
    Climate: 0,
    Birthrate: 46.6,
    Deathrate: 20.34,
    Agriculture: 0.38,
   
  }

This is the mongo collection data. i need to find all the countries Birthrate lower than 20, group them by region, find lowest birthrate region

db.data.aggregate([{$group:{_id:{Country:"$Country"},Birthrate:{$lt:20}}}])

I tried this but not getting output.

CodePudding user response：

You can use this aggregation query:

First $match only birthrates lower than desired value.
Then $sort by Birthrate to get the lowest value at first position.
And last $group by region using $first. As documents are sorted, the first value will be the lowest.

db.collection.aggregate([
  {
    "$match": {
      "Birthrate": {
        "$lt": 20
      }
    }
  },
  {
    "$sort": {
      "Birthrate": 1
    }
  },
  {
    "$group": {
      "_id": "$Region",
      "Birthrate": {
        "$first": "$Birthrate"
      }
    }
  }
])

Example here.

Also if you want to get all values from the document and not only Birthrate you can use "$first": "$$ROOT" like this example