0

We're attempting to port over our real estate property search from SQL Server to MongoDB. Every property can have multiple listings, which we're storing directly in each record as a child array under the property like so:

    {
        "propertyId": 18023335652,
        "latitude": 33.67654,
        "longitude": -117.790335,
        "listings": [{
                "orgId": "",
                "listingId": "",
                "offMarketDate": "2001-07-06T00:00:00",
                "soldPrice": 273000,
                "bedrooms": 3,
                "bathrooms": 3,
                "livingAreaInSqFt": 1653,
                "yearBuilt": 1980,
                "rank": 3
            },
            {
                "orgId": "caclaw-n",
                "listingId": "11234029",
                "offMarketDate": "2015-02-12T00:00:00",
                "soldPrice": 325000,
                "bedrooms": 4,
                "bathrooms": 3,
                "livingAreaInSqFt": 1646,
                "yearBuilt": 1980,
                "rank": 2
            }
        ]
    }
}

When importing the properties/listings into MongoDB, we have business logic that determines a "rank" for each listing so we know which one is "preferred" and should be displayed for a given property to a certain user. It's not as simple as just setting a "isPreferred" value on each listing or using the listing with a Rank = 1 because the user executing the property search might not have access to certain listings (they're in a different MLS). I want to write a MongoDB query that does the following:

  1. Filters properties by their listing values (e.g. Listings.Bedrooms = 3)
  2. Filters each remaining property's listings to only include the preferred one (with the lowest Listings.Rank).
  3. Sort the remaining properties by some field (e.g. Listings.Bedroom asc) but make sure the sort is applied to the preferred listing.

How would I go about doing this in MongoDB?

Justin
  • 17,670
  • 38
  • 132
  • 201
  • 1
    Note that any sort keyed of the largest or smallest value from a property within an array is actually the default behavior of ascending or descending. One way or another you come down to `$unwind` and either `$first` or `$last` depending on your `$sort` direction. Optionally `$arrayElemAt` with the corresponding index of the `$min` rank. – Neil Lunn Mar 30 '19 at 00:46
  • 1
    Also [Return Each Item with the Lowest Value from the Inner Array](https://stackoverflow.com/a/46194999/2313887). Sadly answered and then the person asking just walked away. Shows application of the things you need to do, except the `$sort`. But that's just the path to the resulting property since you only want one result from the array. – Neil Lunn Mar 30 '19 at 00:57

0 Answers0