Elasticsearch 8.5 scenarios to Sort Results by Nested Field with Multiple Conditions
I need some guidance on I need help solving I'm wondering if anyone has experience with I'm working with Elasticsearch 8.5 and trying to sort my search results based on a nested field that contains multiple conditions... My documents have a nested structure for 'comments', and I need to return results sorted by the latest comment date while also filtering to include only comments that are marked as 'approved'. I've tried using a nested query along with sorting, but I'm not getting the results I expect. Here's the mapping for my index: ```json { "mappings": { "properties": { "comments": { "type": "nested", "properties": { "date": { "type": "date" }, "status": { "type": "keyword" } } } } } } ``` And here's the query I'm using to search: ```json { "query": { "nested": { "path": "comments", "query": { "bool": { "must": [ { "match": { "comments.status": "approved" }} ] } }, "inner_hits": {} } }, "sort": [ { "comments.date": { "order": "desc" }} ] } ``` However, this results in an behavior: `"sort" field [comments.date] want to be used in a top-level sort when the query is a nested query`. I've tried various combinations, including using a script sort, but the performance is awful and it doesn't seem to sort correctly. Additionally, I've looked into aggregating the comments to find the latest date first, but I'm unsure how to incorporate that into my sorting. Is there a recommended way to achieve this sort of nested sorting in Elasticsearch? Any insights or examples would be greatly appreciated! What am I doing wrong? The project is a service built with Json. The stack includes Json and several other technologies. How would you solve this? I'm on Debian using the latest version of Json.