How to sort and aggs the result in nested element

Elastic Stack Elasticsearch
1

Hello, I'm new to the Elasticsearch and I faced a problem while developing an ES Query.
I have an index of reports of patients that every doc is like this one. So each report of one patient

{
  "patient": {
    "gender": "female", // male | female | other | unknown
    "birthDate": "2001-12-04"
  },
  "followUps": [
    {
      "followUpDate": "2020-12-15",
      "followUpStatus": "" // (still needs to be codified)
    },
    {
      "followUpDate": "2020-06-15",
      "followUpStatus": "" // (still needs to be codified)
    }
  ]
}

So I want for each report to extract the most recent followup and then count the number of reports that have specif followup status in the most recent followup. Can you help me please, and thank you!

This is what I tried:

{
    "size": 0,
    "query": {
        "bool": {
            "filter": [
                {
                    "range": {
                        "arvTreatmentStarted.effectivePeriod.start": {
                            "lte": "2021-09-11||/d"
                        }
                    }
                },
                {
                    "term": {
                        "arvTreatmentStarted.started": "started"
                    }
                },
                {
                    "nested": {
                        "path": "followUps",
                        "query": {
                            "bool": {
                                "filter": [
                                    {
                                        "bool": {
                                            "must": [
                                                {
                                                    "term": {
                                                        "followUps.firstFollowUp": true
                                                    }
                                                },
                                                {
                                                    "range": {
                                                        "followUps.followUpDate": {
                                                            "lte": "2021-09-11||/d"
                                                        }
                                                    }
                                                }
                                            ]
                                        }
                                    }
                                ]
                            }
                        }
                    }
                }
            ]
        }
    },
    "aggs": {
        "ByRegion": {
            "terms": {
                "field": "facility.region"
            },
            "aggs": {
                "BySubCity": {
                    "terms": {
                        "field": "facility.subCity"
                    },
                    "aggs": {
                        "ByFacility": {
                            "terms": {
                                "field": "facility.healthFacility"
                            },
                            "aggs": {
                                "lostInPreviousMonth": {
                                    "nested": {
                                        "path": "followUps"
                                    },
                                    "aggs": {
                                        "filtred_followups": {
                                            "filter": {
                                                "range": {
                                                    "followUps.followUpDate": {
                                                        "lte": "2021-08-11||/d"
                                                    }
                                                }
                                            },
                                            "aggs": {
                                                "group_docs": {
                                                    "top_hits": {
                                                        "size": 1,
                                                        "sort": [
                                                            {
                                                                "followUps.followUpDate": {
                                                                    "order": "desc"
                                                                }
                                                            }
                                                        ]
                                                    }
                                                },
                                                "aggs": {
                                                    "filter": {
                                                        "terms": {
                                                            "followUps.followUpStatus": [
                                                                "Drop",
                                                                "Lost"
                                                            ]
                                                        }
                                                    }
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}

the problem starts from "lostInPreviousMonth"

2

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.