Sorting only by matching element in Array

MongoDB Atlas Atlas Search
1

I am using Atlas Search and I am struggling to figure out how to do something that would normally be done with a type of unwind/project. I should note that the search and results are returning fine, but it is a sorting problem, probably a result of how I am storing things.

Reasons I am struggling:

  1. Search Must be the first stage in the pipeline
  2. If I move the “SORT” to a separate stage in the Atlas Search pipeline(after SEARCH) it works, but it is slow. This is in the documentation and understandable, but perhaps my only option

This post described my problem well(if external links are alllowed): Retrieve only the queried element in an object array in MongoDB collection - Stack Overflow

Imagine a record set like movies, and yes I realize in this silly example I could probably store the data differently to avoid this, but it highlights what I am trying to do.

{
Name:"a horror film",
type: "horror",
FriendsThatWatched:
[
{
Name:"bob",
WachedOn: ISODate('2023-11-10T05:40:40.961+00:00')
},
{
Name:"sarah",
WachedOn: ISODate('2022-11-10T05:40:40.961+00:00')
}
]
},
{
Name:"a comedy film",
type: "comedy",
FriendsThatWatched:
[
{
Name:"bob",
WachedOn: ISODate('2023-12-10T05:40:40.961+00:00')
},
{
Name:"sarah",
WachedOn: ISODate('2021-11-10T05:40:40.961+00:00')
}
]
}

So let’s say I do a search for all movies bob watched or whatever, no problem I get those hits, but if I try to sort by “WatchedOn”, to get the results in the order that he watched them it will use “sarahs” watch dates, presumably ordering by the oldest/newest date in the entire array(?) instead of restricting the sort to only the matching elements of “bob”.

I do understand WHY this is happening, but I am just not sure what to do about it other than moving sort out of the Atlas Search and into a later stage of the pipeline where I use projection to limit the values of the array returned, which causes performance issues. Maybe what I am doing is just too complicated or my data structure needs a tweak, but the structure works great for every other application use case so I would prefer to avoid changing it for this one type of sort.

Any ideas?

2

I should have gone with Cunningham’s law, but I think I am answering my own question a bit here…

I was able to execute a creative use of $redact to successfully limit the array to only items I care about, which helped with my sorting issue.

However, in order to redact before sorting, I did have to remove sort from the Atlas Search “format”(not sure what the proper term is here, and put the sort as it’s own stage in the pipeline after search. This did cause a performance issue as expected, but it seems tolerable.

The other thing I may try later is an unwind first, and while I am sure that would work, it would require me to change my filters/a bit, which I wanted to avoid if possible due to the way they are generated and the structure of data I am working with.