Sorting, Paginating the aggregated data

Elastic Stack Elasticsearch
1

I need to aggregate the data on the basis of two fields (composite aggregation), sort all the data and then perform pagination. Is is possible using elastic search?

If Yes, Can you please share an example.

2

It's one of those "it depends" answers.
This wizard will help run through the typical questions that can lead to different answers

3

Problem that I am facing is when I am using Composite aggregation, I am not able to sort the buckets on sub-aggregation results. It is only sorting the returned buckets. Is there any way to sort all the buckets of composite aggregation using sub-aggregation field.

4

No, composite aggs are sorted on the choice of key, not the value of some sub aggregation. Hence the question line in the wizard Plunker_-_Simple_AngularJS_decision_tree_wizard

5

This is suggesting terms aggregation. In terms aggregation, I am facing issue with sorting and pagination when aggregating on the basis of two fields. Following is the JSON example:

{
  "size": 0,
  "aggs": {
  	"group_by_product" : {
  		"terms" : {
  			"field" : "product.keyword"
  		},
  		"aggs" : {
  			"group_by_brand" : {
  	    	    "terms" : {
  		    	    "field" : "brand"
  		        },
				"aggs" : {
					"totalProducts" : {
						"sum" : {
							"field" : "total"
						}
					}
				}
  			}
  		}
  	}
  }
}

I have to sort all the products on the basis of "totalProducts" and perform pagination. Is there any way to achieve that?

If Yes, can you please provide me the example.

6

I understand what a person's name and age might be but I don't understand what the "record" field represents.
Can I check what the business problem is you are trying to solve?

7

I have also done some correction in the above example. Actually my business problem is

  1. I have to group the data on the basis of two fields.
  2. Call different aggregation functions (like sum, avg, max, etc.) as shown in above example.
  3. Sort the data on the basis of aggregation function field ("totalProducts" as per the above example).
  4. Provide pagination for the returned data.
8

You can use a script in a terms aggregation to combine two document values into a single key.

See the order parameter for the terms agg.

See the warnings in the wizard if you have many shards and many unique keys to sort. There are a number of settings that can help address any accuracy issues

9

So script helps us to group by on the basis of two fields (like "group by product, brand" in SQL)?

10

I tried following request:

{
  "size": 0,
  "aggs": {
  	"groupBy" : {
            "terms" : {
                "script" : {
                    "source": "[doc['product.keyword'].value, doc['brand'].value].join('#')",
                    "lang": "painless"
                },
                "size": 200
            },
	        "aggs": {
	                "totalProducts": {
	                   "sum" : {
								"field" : "total"
							}
	                },
					"sorting" : {
				  		"bucket_sort" :{
				  			"sort" : [
				  				{
				  					"totalProducts" : {
				  						"order" : "desc"
				  					}
				  				}
				  				]
				  		}
				  	}
	        }
        }
  	}
  
}

Still the sorting is performed only on the returned buckets instead of all the buckets.

11

:point_up::point_up::point_up::point_up:

12

Yes, sorting worked correctly now.
Last two questions:

  1. Can we sort on multiple fields at a time?
  2. Do we support pagination in terms aggregation?
13

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