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

score 0 · Answer 1 · answered Sep 06 '22 at 13:13

0

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.

answered Sep 06 '22 at 13:13

Amit

30,756
6
57
88

existing query is working properly to group by xyz and return the latest version. Now i need to sort the aggregated data based on created field ` { "_index": "index", "_type": "doc", "_id": "3", "_version": 2, "_seq_no": 1, "_primary_term": 1, "found": true, "_source": { "doc": { "uuid": "abcd", "type": "strifn", "name": "event_viewer_executes_uncommon_binary.nwr", "version": 3.12, "s3ObjectVersionId": "lPL3v.4FwuKPew.Y.36lQQdeQEeIaAqP", "description": "", "created": 165842829134 } } } ` – Puneet Dewan Sep 07 '22 at 06:15
@PuneetDewan i don't see the index mapping and sample and expected documents – Amit Sep 07 '22 at 06:15
@PuneetDewan, you justy added a single document, can you add few more and show your required result set out of those sample documents so that I can work on your data set – Amit Sep 07 '22 at 06:59
I have added what the current query returns. I need the hits in sorted format by created field – Puneet Dewan Sep 07 '22 at 08:34
@PuneetDewan are you able to solve issue? – Amit Jan 12 '23 at 11:35

Sorting aggregated data in elastic search

1 Answers1