Sorting aggregated data in elastic search

Question

I am doing a search that is doing an aggregation by xyz field and getting the latest version. Now I need to sort the aggregated data based on created field. Let me know how we can do that.

{
    "query": {
        "query_string": {
            "query": ""
        }
    },
    "aggs": {
        "uuid": {
            "terms": {
                "field": "xyz.keyword"
            },
            "aggs": {
                "top_trades_hits": {
                    "top_hits": {
                        "sort": [
                            {
                                "version": {
                                    "order": "desc"
                                }
                            }
                        ],
                        "size": 1
                    }
                }
            }
        }
    }
}

the Above mentioned query returns

{
    "aggregations": {
        "uuid": {
            "doc_count_error_upper_bound": 0,
            "sum_other_doc_count": 0,
            "buckets": [
                {
                    "key": "794a5b8f-3e22-4ff9-98bb-b8b54c85948e",
                    "doc_count": 3,
                    "agg": {
                        "hits": {
                            "total": {
                                "value": 3,
                                "relation": "eq"
                            },
                            "max_score": null,
                            "hits": [
                                {
                                    "_index": "index",
                                    "_type": "doc",
                                    "_id": "7",
                                    "_score": null,
                                    "_source": {
                                        "uuid": "794a5b8f-3e22-4ff9-98bb-b8b54c85948e",
                                        "type": "qsdn",
                                        "discontinued": false,
                                        "minSupportedPlatformVersion": "11.5.3.3",
                                        "version": 2,
                                        "created": 1658428291346
                                    },
                                    "sort": [
                                        2
                                    ]
                                }
                            ]
                        }
                    }
                },
                {
                    "key": "03504029-a029-417d-bd67-fb1b5fc5055b",
                    "doc_count": 2,
                    "agg": {
                        "hits": {
                            "total": {
                                "value": 2,
                                "relation": "eq"
                            },
                            "max_score": null,
                            "hits": [
                                {
                                    "_index": "index",
                                    "_type": "doc",
                                    "_id": "9",
                                    "_score": null,
                                    "_source": {
                                        "uuid": "03504029-a029-417d-bd67-fb1b5fc5055b",
                                        "type": "gdsg",
                                        "discontinued": false,
                                        "version": 1.1,
                                        
                                        "created": 1554904300799
                                    },
                                    "sort": [
                                        1.1
                                    ]
                                }
                            ]
                        }
                    }
                }
            ]
        }
    }
}

Document for the elastic search is as follows

{
    "_index": "index",
    "_type": "doc",
    "_id": "3",
    "_version": 2,
    "_seq_no": 1,
    "_primary_term": 1,
    "found": true,
    "_source": {
        "doc": {
            "uuid": "abcd",
            "type": "strifn",
            "name": "default",
            "version": 3.12,
            "s3ObjectVersionId": "",
            "created": 165842829134
        }
    }
}

Expected result

{
    "aggregations": {
        "uuid": {
            "doc_count_error_upper_bound": 0,
            "sum_other_doc_count": 0,
            "buckets": [
                {
                    "key": "03504029-a029-417d-bd67-fb1b5fc5055b",
                    "doc_count": 2,
                    "agg": {
                        "hits": {
                            "total": {
                                "value": 2,
                                "relation": "eq"
                            },
                            "max_score": null,
                            "hits": [
                                {
                                    "_index": "index",
                                    "_type": "doc",
                                    "_id": "9",
                                    "_score": null,
                                    "_source": {
                                        "uuid": "03504029-a029-417d-bd67-fb1b5fc5055b",
                                        "type": "gdsg",
                                        "discontinued": false,
                                        "version": 1.1,
                                        
                                        "created": 1554904300799
                                    },
                                    "sort": [
                                        1.1
                                    ]
                                }
                            ]
                        }
                    }
                },
                {
                    "key": "794a5b8f-3e22-4ff9-98bb-b8b54c85948e",
                    "doc_count": 3,
                    "agg": {
                        "hits": {
                            "total": {
                                "value": 3,
                                "relation": "eq"
                            },
                            "max_score": null,
                            "hits": [
                                {
                                    "_index": "index",
                                    "_type": "doc",
                                    "_id": "7",
                                    "_score": null,
                                    "_source": {
                                        "uuid": "794a5b8f-3e22-4ff9-98bb-b8b54c85948e",
                                        "type": "qsdn",
                                        "discontinued": false,
                                        "minSupportedPlatformVersion": "11.5.3.3",
                                        "version": 2,
                                        "created": 1658428291346
                                    },
                                    "sort": [
                                        2
                                    ]
                                }
                            ]
                        }
                    }
                }
                
            ]
            }
    }
}

I am using AWS opensearch for the same

userJRWg · Answer

Your query is correct only, you just need to increase the size from 1 to see all the documents in your bucket sorted according to version field in your Elasticsearch index.

Can you share more info, if above doesn't help you, like sample documents and index mapping.

Sorting aggregated data in elastic search

1 Answers