CODEWITHSUNDEEP
pythonmongodb
James Ayres
James Ayres

Reputation: 91

How to find documents that have the max and min values in an array in MongoDB

I have been struggling with this for a while. I have state and vacation home data. I need to find the number of each kind of vacation home and return the type of properties that have the most and least amount of locations. I have been able to group by state and count the number of properties in an array. I just need to pull the most and least property.

The data I have...

{'_id': 'CA', 'properties': [{'property_type': 'Camper/RV', 'quantity': 1}, {'property_type': 'Hostel', 'quantity': 2}, ...]}
{'_id': 'BR', 'properties': [{'property_type': 'Guest suite', 'quantity': 3}, {'property_type': 'Bed and breakfast', 'quantity': 7}, ...]}
...

And I want to write a query that that returns data that looks like this

{'_id': 'TR', 'most_type': 'Apartment', 'most_number': 53, 'least_type': 'Camper/RV', 'least_number': 5}
{'_id': 'CA', 'most_type': 'House', 'most_number': 53, 'least_type': 'TinyHouse', 'least_number': 3}
...

Upvotes: 0

Views: 106

Answers (1)

AlexisG
AlexisG

Reputation: 2484

You use an aggregate

db.collection.aggregate([
  {
    "$unwind": "$properties"
  },
  {
    "$sort": {
      "properties.quantity": -1
    }
  },
  {
    "$group": {
      "_id": "$_id",
      "most_type": {
        "$first": "$properties.property_type"
      },
      "most_number": {
        "$first": "$properties.quantity"
      },
      "least_type": {
        "$last": "$properties.property_type"
      },
      "least_number": {
        "$last": "$properties.quantity"
      }
    }
  }
])

Try it here

Upvotes: 1

Related Questions