Sort parent records by a certain child array element in MongoDB [duplicate]

manpreet Best Answer 3 years ago

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?