Paging and sorting results after a term aggregation by doc fields

Elastic Stack Elasticsearch
1

Hi everyone,
what i'm trying to do is sorting the results from a term aggregation using the fields in the starting document, the final goal is to be able sorting and paging a datatable server side because there're too many records to load in a single call. i tried with bucket_sort aggregation but it allows only the pagination but not to sort by fields
elastic version 7.0.1

here is the query that i'm using:

{
"size": 0,
"query": {
    "bool": {
    
        "should": [
            {
                "term": {
                    "check_field__one.keyword": {
                        "value": "00010",
                        "boost": 1.0
                    }
                }
            },
            {
                "term": {
                    "check_field__two.keyword": {
                        "value": "00010",
                        "boost": 1.0
                    }
                }
            }
        ],
        "adjust_pure_negative": true,
        "minimum_should_match": "1",
        "boost": 1.0
    }
},

"aggregations": {
    "by_ID": {
        "terms": {
            "field": "_ID.keyword",
            "size": 10000,
            "min_doc_count": 1,
            "shard_min_doc_count": 0,
            "show_term_doc_count_error": false,
            "order": [
                {
                    "_count": "desc"
                },
                {
                    "_key": "asc"
                }
            ]
        },
        "aggregations": {
            "min_START_DATE": {
                "min": {
                    "field": "TMS_START_DATE"
                }
            },
            "max_END_DATE": {
                "max": {
                    "field": "TMS_END_DATE",
                    "missing": 9223372036854775807
                }
            },
            
            "top_START_DATE": {
                "top_hits": {
                    "from": 0,
                    "size": 1,
                    "version": false,
                    "seq_no_primary_term": false,
                    "explain": false,
                    "_source": {
                        "includes": [
                           ...fields to show in table
                        ],
                        "excludes": []
                    },
                    "sort": [
                        {
                            "TMS_START_DATE": {
                                "order": "desc"
                            }
                        }
                    ]
                }
            },
           
            // that stage allows the pagination but not the sort by doc fields
            "sort": {
                "bucket_sort": {
                    "sort": [],
                    "from": 0,
                    "size": 10,
                    "gap_policy": "SKIP"
                }
            }
        }
    }
  }
}

Thanks in advance

2

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.