Sorting the data based on date in Elasticseach using the transform

Elastic Stack Elasticsearch
1

Hi Team,

We are using a sort in our transform to sort the records based on a timestamp field by grouping them on a unique key and get only the latest record out of that. But we are facing an issue as in one use case my timestamp is exactly same for all the group of records inside for a key.
example:

Doc1:
txn id(unique key) -> 12345
date -> Sep 17, 2024 @ 13:33:57.000
ingest time -> Sep 17, 2024 @ 23:33:57.000
status -> success

Doc2:
txn id(unique key) -> 12345
date -> Sep 17, 2024 @ 13:33:57.000
ingest time -> Sep 17, 2024 @ 23:36:57.000
status -> completed

When i am sorting always i am getting status = success record instead of status = completed. Could you please help me how elastic will sort and fetch the records in this case and is there any way to solve this issue.

Note: I cannot use ingest time for sorting as my other usecases will fail.

2

Hi @venkatkumar229

Could you share your query here to clarify?

3

Hi @Alex_Salgado-Elastic ,

Please find the below transform config.

POST _transform/_preview
{
  "source": {
    "index": [
      "xxxxxxxxxxx"
    ],
    "query": {
		"match_all": {}
    }
  },
  "dest": {
    "index": "xxxxxxxxx"
  },
  "sync": {
    "time": {
      "field": "ingest.time",
      "delay": "60s"
    }
  },
  "pivot": {
    "group_by": {
      "txn_id": {
        "terms": {
          "field": "txn_id"
        }
      }
    },
    "aggregations": {
      "date": {
        "max": {
          "field": "date"
        }
      },
      "base_fields": {
        "top_metrics": {
          "metrics": [
            {
              "field": "txn_type",
              "missing": ""
            },
            {
              "field": "status",
              "missing": ""
            }
          ],
          "sort": [{
            "date": "desc"
          }]
        }
      }
    }
  },
  "settings": {}
}