MongoDB Aggregation: How to project a Object in an Array, within an Object-CodePudding

I have some data


        {
        _id: ObejctId('343243324324')
        name: "NZ"
        country_info: {
              phone_system: 123,
              states: [
                  {
                      state_name: "ABC",
                      populated: 12
                  },
                  {
                      state_name: "DEF",
                      populated: 22
                  },
                  {
                      state_name: "GHI",
                      populated: 55
                  }
                ] 
         }
       },
    
           {
            _id: ObejctId('3546546466544')
            name: "Oz"
            country_info: {
                  phone_system: 456,
                  states: [
                      {
                          state_name: "ABC",
                          population: 8
                      },
                      {
                          state_name: "DEF",
                          population: 5
                      },
                      {
                          state_name: "GHI",
                          population: 3
                      }
                    ] 
             }
           },

How do I get all the populations of states named "DEF" ?

Ideally, this is what I am after in the results:

{name: "NZ", population: 22},{name: "oZ", population: 5}

I have tried lots of combinations of project and expression and filter all to no avail. I am ok (ish) with general mongo, but once it starts to go into Arrays and Objects I fail. Are there any good videos I should watch also?

CodePudding user response：

You can do it with Aggregation framework like this:

$match - to first filter only the documents that state with the name "DEF".
$unwind - to unwind the states array.
$match - to filter only states where state name is "DEF".
$project - to project data to desired output.

db.collection.aggregate([
  {
    "$match": {
      "country_info.states.state_name": "DEF"
    }
  },
  {
    "$unwind": "$country_info.states"
  },
  {
    "$match": {
      "country_info.states.state_name": "DEF"
    }
  },
  {
    "$project": {
      "name": 1,
      "population": "$country_info.states.population"
    }
  }
])

Working example