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

Question

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?

NeNaD · Accepted Answer · 2022-12-21 23:36:21Z

1

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

answered Dec 21, 2022 at 23:36

NeNaD

20.8k11 gold badges64 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Matt Over a year ago

your a champion, thank you. No way I would have gotten this on my own.

NeNaD Over a year ago

You are welcome! Consider also accepting and upvoting my answer, so other people that have the same issue would see it worked for you.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related