Fetch latest records by date in elasticsearch

user_16896291 0
Viewed 29

I want to fetch latest visits of patients that belongs to a doctor.

Example data:

{
doctor_id:"abcd",
"patient_id":"pat1",
"created_date":"2022-08-30T15:26:19.171Z"
"patient_data":"some data belongs to patient"
},
{
doctor_id:"abcd",
"patient_id":"pat1",
"created_date":"2022-08-29T15:26:19.171Z"
"patient_data":"some data belongs to patient"
},
{
doctor_id:"abcd",
"patient_id":"pat2",
"created_date":"2022-08-30T15:26:19.171Z"
"patient_data":"some data belongs to patient"
},
{
doctor_id:"abcd",
"patient_id":"pat2",
"created_date":"2022-08-29T15:26:19.171Z"
"patient_data":"some data belongs to patient"
},

Is it possible to get the latest record of each patient:

{
doctor_id:"abcd",
"patient_id":"pat1",
"created_date":"2022-08-30T15:26:19.171Z"
"patient_data":"some data belongs to patient"
},
{
doctor_id:"abcd",
"patient_id":"pat2",
"created_date":"2022-08-30T15:26:19.171Z"
"patient_data":"some data belongs to patient"
}

What should I follow to do get results like this.

UPDATE: I am using below query now:

{
    "query":{
                "bool": {
            "must": [
                {
                    "term": {
                        "doctor_id.keyword": "abcd"
                    }
                }
            ]
        }
    },

      "aggs": {
        "latest_created_date": {
          "top_hits": {
            "sort": [
              {
                "created_date": {
                  "order": "desc"
                }
              }
            ],
            "size": 1
          }
        }
      }
    }
  }

Using this query, in the aggregations, I am only seeing one record which is the latest by date.

I want atleast one record of each patient by latest date.

elasticsearch-aggregation elasticsearch
2 Answers

You are almost there, but you need to use the top_hits aggs as a sub aggregation and needs a terms aggregation on patient_id field to get the desired output. I've tried below query on your sample data and its giving me the expected output.

{
    "size": 0,
    "query": {
        "bool": {
            "must": [
                {
                    "term": {
                        "doctor_id.keyword": "abcd"
                    }
                }
            ]
        }
    },
    "aggs": {
        "unique_patients": {
            "terms": {
                "field": "patient_id.keyword" --> unique patient this terms aggs will return. 
            },
            "aggs": {
                "latest_created_date": { --> latest created date in the buckets return by terms aggs.
                    "top_hits": {
                        "_source": {
                            "includes": [
                                "patient_id",
                                "doctor_id",
                                "created_date",
                                "patient_data"
                            ],
                            "excludes": []
                        },
                        "sort": [
                            {
                                "created_date": {
                                    "order": "desc"
                                }
                            }
                        ],
                        "size": 1
                    }
                }
            }
        }
    }
}

And Result

{
    "took": 13,
    "timed_out": false,
    "_shards": {
        "total": 1,
        "successful": 1,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": {
            "value": 4,
            "relation": "eq"
        },
        "max_score": null,
        "hits": []
    },
    "aggregations": {
        "unique_patients": { 
            "doc_count_error_upper_bound": 0,
            "sum_other_doc_count": 0,
            "buckets": [
                {
                    "key": "pat1",
                    "doc_count": 3,
                    "latest_created_date": {
                        "hits": {
                            "total": {
                                "value": 3,
                                "relation": "eq"
                            },
                            "max_score": null,
                            "hits": [
                                {
                                    "_index": "73564565",
                                    "_id": "1",
                                    "_score": null,
                                    "_source": {
                                        "doctor_id": "abcd",
                                        "patient_id": "pat1",
                                        "created_date": "2022-08-30T15:26:19.171Z",
                                        "patient_data": "some data belongs to patient"
                                    },
                                    "sort": [
                                        1661873179171
                                    ]
                                }
                            ]
                        }
                    }
                },
                {
                    "key": "pat2",
                    "doc_count": 1,
                    "latest_created_date": {
                        "hits": {
                            "total": {
                                "value": 1,
                                "relation": "eq"
                            },
                            "max_score": null,
                            "hits": [
                                {
                                    "_index": "73564565",
                                    "_id": "3",
                                    "_score": null,
                                    "_source": {
                                        "doctor_id": "abcd",
                                        "patient_id": "pat2",
                                        "created_date": "2022-08-30T15:26:19.171Z",
                                        "patient_data": "some data belongs to patient"
                                    },
                                    "sort": [
                                        1661873179171
                                    ]
                                }
                            ]
                        }
                    }
                }
            ]
        }
    }
}
user_40394310

use a top hits agg, something like;

POST /index/_search?size=0
{
  "aggs": {
      "aggs": {
        "latest_created_date": {
          "top_hits": {
            "sort": [
              {
                "created_date": {
                  "order": "desc"
                }
              }
            ],
            "size": 1
          }
        }
      }
    }
  }
}
user_156281200
Related