How to get one sorted document from group?

Question

I have many documents, like this:

    "_source": {
      "id": "30e7fb3a-f07f-4ee0-b4dd-3604a7d1b206",
      "processId": "cd88e9bc-2375-4c4a-80ef-7ec715e2c859",
      "createdAt": "2024-11-14T17:56:36.694615+03:00",
      "updatedAt": "2024-11-14T17:56:36.694873+03:00",
      "completedAt": "2024-11-14T17:56:36.694873+03:00"
    },
    "_source": {
      "id": "f93e926c-9370-45a9-9a16-4e4a7c3c2a94",
      "processId": "cd88e9bc-2375-4c4a-80ef-7ec715e2c859",
      "createdAt": "2024-11-14T17:56:36.595321+03:00",
      "updatedAt": "2024-11-14T17:56:36.694341+03:00",
      "completedAt": "2024-11-14T17:56:36.694341+03:00"
    },
    "_source": {
      "id": "d71e2538-b763-47b9-bb1e-67b67f640532",
      "processId": "Process_b77f8ac",
      "createdAt": "2024-11-11T14:51:19.548631+03:00",
      "updatedAt": "2024-11-11T14:51:19.554184+03:00",
      "completedAt": "2024-11-11T14:51:19.554184+03:00"
    }

I need to obtain all documents matching adjusted array of processId (or only one processId, does not matters), & return only one document for each processId with latest datetime by field updatedAt.

Is it possible to preform in one query?

Answer 1

Hi Вячеслав Чернышов,

If I understand your question correctly, you would like to pass a processId in a query (which may match multiple documents), and only return a single document with the most recent updatedAt field.

This is definitely possible within OpenSearch/Elasticsearch. The first pre-requisite is that your index mappings are set correctly so that the updatedAt field is mapped as a datetime field.

As long as that's done, the following query should meet your needs.

{
    "query": {
        "match": {
            "processId": "<your-process-id>"
        }
    },
    "sort": {
        "updatedAt": {
            "order": "desc"
        }
    },
    "_size": 1 # to only show the first (most recent) doc
}

I hope this helps. Let me know if I've misunderstood.