My use case is a bit strange. I’d like to use Atlas search (full text search) because I want the ability to filter on arbitrary columns (the collection has a dynamic Atlas search that covers all columns). I also want to have the search result sorted by a given column.
Here are my findings:
It seems the “$search” operator in the aggregation pipeline doesn’t support sorting. If I use “$sort” outside of “$search”, sorting basically happens in memory after search is done. With a large collection (eg: hundreds of millions), this would be very slow well.
The closest I can find is the “$near” operator (with a heavy weight), which can be used to mimic sorting because results will be returned by how close they are to the column (to which $near is applied). One limitation of $near is that it only supports “number” and “date” columns.
Elastic Search has no trouble sorting results based on any column of any type as far as I can tell.
Is my understanding correct? Thanks for your expert input!