# ES, aggregation and pagination

**URL:** <https://discuss.elastic.co/t/es-aggregation-and-pagination/16601>\
**Category:** Elasticsearch\
**Created:** [March 26, 2014, 8:39am UTC](https://discuss.elastic.co/t/es-aggregation-and-pagination/16601 "2014-03-26T08:39:50Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![bob](https://avatars.discourse-cdn.com/v4/letter/b/ea666f/32.png) [@bob](https://discuss.elastic.co/u/bob)\
**Post date:** [March 26, 2014, 8:39am UTC](https://discuss.elastic.co/t/es-aggregation-and-pagination/16601/1 "2014-03-26T08:39:50Z")

</div>

Hi everyone,

I'm currently working on an ES based project (ES version in use 1.0.1) and I'm fiddling with the ES syntax to make aggregations work.  
Here is what I'm trying to do : I have a set of versioned documents on which I'm trying to perform a search query with pagination, having the following two steps :  
-for each document, get the highest version number (1st inner bucket)  
-then for each document, group by their id and sort the resulting set with the metric Score (from the inner bucket) in order to maintain a decent pagination

Anyways, here is the search query :

{  
"fields":[

],  
"size":0,  
"from":0,  
"sort":[  
"\_score"  
],  
"query":{  
"filtered":{  
"query":{  
"bool":{  
"should":[  
{  
"multi\_match":{  
"query":"general",  
"fields":[  
"Title.Value.original^4",  
"Title.Value.partial"  
]  
}  
}  
],  
"minimum\_should\_match":1  
}  
}  
}  
},  
"aggregations":{  
"Count":{  
"cardinality":{  
"field":"IDDocument"  
}  
},  
"IDDocumentPage":{  
"terms":{  
"field":"IDDocument",  
"order":{  
"IDDocument\>Score":"desc"  
}  
},  
"aggregations":{  
"IDDocument":{  
"terms":{  
"field":"IDDocument",  
"order":{  
"Score":"desc"  
}  
},  
"aggregations":{  
"VersionNumber":{  
"max":{  
"field":"VersionNumber"  
}  
},  
"Score":{  
"max":{  
"script":"\_doc.score"  
}  
}  
}  
}  
}  
}  
}  
}

Which fails, yielding the following error :

AggregationExecutionException[terms aggregation [IDDocumentPage] is configured with a sub-aggregation order [IDDocument\>Score] but no sub aggregation with this name is configured];

Am I missing something here, syntax-wise ?

Here is another attempt based on a filter range syntax, which runs alright but doesn't produce any result.

{  
"fields":[

],  
"size":0,  
"from":0,  
"sort":[  
"\_score"  
],  
"query":{  
"filtered":{  
"query":{  
"bool":{  
"should":[  
{  
"multi\_match":{  
"query":"general",  
"fields":[  
"Title.Value.original^4",  
"Title.Value.partial"  
]  
}  
}  
],  
"minimum\_should\_match":1  
}  
}  
}  
},  
"aggregations":{  
"Count":{  
"cardinality":{  
"field":"IDDocument"  
}  
},  
"IDDocumentPage":{  
"filter" : { "range" : { "IDDocument\>Score" : { "gt" : 0 } } },  
"aggregations":{  
"IDDocument":{  
"terms":{  
"field":"IDDocument",  
"order":{  
"Score":"desc"  
}  
},  
"aggregations":{  
"VersionNumber":{  
"max":{  
"field":"VersionNumber"  
}  
},  
"Score":{  
"max":{  
"script":"\_doc.score"  
}  
}  
}  
}  
}  
}  
}  
}

Any idea on what I'm doing wrong here ? And more generally what would be the (best) way to perform a group by and pagination in ES at the same time ?

---

<div class="post-metadata">

**Author:** ![jpountz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jpountz/32/45836_2.png) [@jpountz](https://discuss.elastic.co/u/jpountz)\
**Post date:** [March 26, 2014, 8:52am UTC](https://discuss.elastic.co/t/es-aggregation-and-pagination/16601/2 "2014-03-26T08:52:12Z")

</div>

Hi Bob,

Although you reported using Elasticsearch 1.0.1, you seem to be using  
features that are only available in Elasticsearch 1.1.0: the cardinality  
aggregation and the ability to sort according by several levels of nested  
aggregations. That might partially explain the issue that you are  
encoutering?

Regarding pagination of the terms aggregation (which is the closest thing  
we have to a GROUP BY), this is not supported.

On Wed, Mar 26, 2014 at 9:39 AM, bob [bkoenig@groupama-ge.fr](mailto:bkoenig@groupama-ge.fr) wrote:

> Hi everyone,
> 
> I'm currently working on an ES based project (ES version in use 1.0.1) and  
> I'm fiddling with the ES syntax to make aggregations work.  
> Here is what I'm trying to do : I have a set of versioned documents on  
> which  
> I'm trying to perform a search query with pagination, having the following  
> two steps :  
> -for each document, get the highest version number (1st inner bucket)  
> -then for each document, group by their id and sort the resulting set with  
> the metric Score (from the inner bucket) in order to maintain a decent  
> pagination
> 
> Anyways, here is the search query :
> 
> {  
> "fields":[
> 
> ],  
> "size":0,  
> "from":0,  
> "sort":[  
> "\_score"  
> ],  
> "query":{  
> "filtered":{  
> "query":{  
> "bool":{  
> "should":[  
> {  
> "multi\_match":{  
> "query":"general",  
> "fields":[  
> "Title.Value.original^4",  
> "Title.Value.partial"  
> ]  
> }  
> }  
> ],  
> "minimum\_should\_match":1  
> }  
> }  
> }  
> },  
> "aggregations":{  
> "Count":{  
> "cardinality":{  
> "field":"IDDocument"  
> }  
> },  
> "IDDocumentPage":{  
> "terms":{  
> "field":"IDDocument",  
> "order":{  
> "IDDocument\>Score":"desc"  
> }  
> },  
> "aggregations":{  
> "IDDocument":{  
> "terms":{  
> "field":"IDDocument",  
> "order":{  
> "Score":"desc"  
> }  
> },  
> "aggregations":{  
> "VersionNumber":{  
> "max":{  
> "field":"VersionNumber"  
> }  
> },  
> "Score":{  
> "max":{  
> "script":"\_doc.score"  
> }  
> }  
> }  
> }  
> }  
> }  
> }  
> }
> 
> Which fails, yielding the following error :
> 
> AggregationExecutionException[terms aggregation [IDDocumentPage] is  
> configured with a sub-aggregation order [IDDocument\>Score] but no sub  
> aggregation with this name is configured];
> 
> Am I missing something here, syntax-wise ?
> 
> Here is another attempt based on a filter range syntax, which runs alright  
> but doesn't produce any result.
> 
> {  
> "fields":[
> 
> ],  
> "size":0,  
> "from":0,  
> "sort":[  
> "\_score"  
> ],  
> "query":{  
> "filtered":{  
> "query":{  
> "bool":{  
> "should":[  
> {  
> "multi\_match":{  
> "query":"general",  
> "fields":[  
> "Title.Value.original^4",  
> "Title.Value.partial"  
> ]  
> }  
> }  
> ],  
> "minimum\_should\_match":1  
> }  
> }  
> }  
> },  
> "aggregations":{  
> "Count":{  
> "cardinality":{  
> "field":"IDDocument"  
> }  
> },  
> "IDDocumentPage":{  
> "filter" : { "range" : { "IDDocument\>Score" : { "gt" : 0 }  
> } },  
> "aggregations":{  
> "IDDocument":{  
> "terms":{  
> "field":"IDDocument",  
> "order":{  
> "Score":"desc"  
> }  
> },  
> "aggregations":{  
> "VersionNumber":{  
> "max":{  
> "field":"VersionNumber"  
> }  
> },  
> "Score":{  
> "max":{  
> "script":"\_doc.score"  
> }  
> }  
> }  
> }  
> }  
> }  
> }  
> }
> 
> Any idea on what I'm doing wrong here ? And more generally what would be  
> the  
> (best) way to perform a group by and pagination in ES at the same time ?
> 
> --  
> View this message in context:  
> [http://elasticsearch-users.115913.n3.nabble.com/ES-aggregation-and-pagination-tp4052774.html](http://elasticsearch-users.115913.n3.nabble.com/ES-aggregation-and-pagination-tp4052774.html)  
> Sent from the Elasticsearch Users mailing list archive at [Nabble.com](http://Nabble.com).
> 
> --  
> You received this message because you are subscribed to the Google Groups  
> "elasticsearch" group.  
> To unsubscribe from this group and stop receiving emails from it, send an  
> email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
> To view this discussion on the web visit  
> [https://groups.google.com/d/msgid/elasticsearch/1395823190787-4052774.post%40n3.nabble.com](https://groups.google.com/d/msgid/elasticsearch/1395823190787-4052774.post%40n3.nabble.com)  
> .  
> For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).

--  
Adrien Grand

--  
You received this message because you are subscribed to the Google Groups "elasticsearch" group.  
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CAL6Z4j5MPJmp06g1P0PrUj-ECX4JAZzhAViNzSCP2XjoDva7eQ%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAL6Z4j5MPJmp06g1P0PrUj-ECX4JAZzhAViNzSCP2XjoDva7eQ%40mail.gmail.com).  
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).

---

<div class="post-metadata">

**Author:** ![bob](https://avatars.discourse-cdn.com/v4/letter/b/ea666f/32.png) [@bob](https://discuss.elastic.co/u/bob)\
**Post date:** [March 27, 2014, 8:45am UTC](https://discuss.elastic.co/t/es-aggregation-and-pagination/16601/3 "2014-03-27T08:45:02Z")

</div>

Hi Adrien,

thanks for your answer. You are right about cardinality, not available on the 1.0.1, but I'm using a plugin to cover this part.  
I switched my ES version to the 1.1.0 and managed to run the following query :

{  
"fields":[

],  
"size":0,  
"from":0,  
"sort":[  
"\_score"  
],  
"query":{  
"filtered":{  
"query":{  
"bool":{  
"should":[  
{  
"multi\_match":{  
"query":"general",  
"fields":[  
"Titre.Valeur.original^4",  
"Titre.Valeur.partial",  
"Resume.Valeur.original^2",  
"Resume.Valeur.partial",  
"Corps.Valeur.original^2",  
"Corps.Valeur.partial"  
]  
}  
}  
],  
"minimum\_should\_match":1  
}  
}  
}  
},  
"aggs" : {  
"Count":{  
"cardinality":{  
"field":"IDDocument"  
}  
},  
"docagg" : {  
"terms" : {  
"field" : "IDDocument",  
"order" : { "version\_max\>score" : "desc" }  
},  
"aggs" : {  
"version\_max" : {  
"filter" : { "range" : { "NumeroVersion" : { "gt" : 0 } }},  
"aggs" : {  
"vMax" : { "max" : { "field" : "NumeroVersion" }},  
"score" : { "max" : { "script":"\_doc.score"}}  
}  
}  
}  
}  
}  
}

As I understand it, pagination can't be achieved with ES, but we are trying to do so anyway in a different manner. Based on the previous query, the idea would be to be able to filter (eg : range filter or else) on the score that is brought up by the inner bucket (version\_max) and set a given size on the docagg bucket.  
The concept here is to set up the very same technique used in SQL with CTEs, here is the example :

declare @Document table (  
IDDocument uniqueidentifier,  
NumeroVersion int,  
Score decimal(18,2)  
);

declare @idDoc1 uniqueidentifier;  
declare @idDoc2 uniqueidentifier;  
declare @idDoc3 uniqueidentifier;

set @idDoc1 = newid();  
set @idDoc2 = newid();  
set @idDoc3 = newid();

insert into @Document values (@idDoc1, 1, 1.75);  
insert into @Document values (@idDoc1, 2, 1.5);  
insert into @Document values (@idDoc2, 1, 0.75);  
insert into @Document values (@idDoc2, 2, 1.25);  
insert into @Document values (@idDoc2, 3, 1.95);  
insert into @Document values (@idDoc3, 1, 2);

select d.IDDocument, max(d.NumeroVersion) as NumeroVersion, max(d.Score) as ScoreDocument  
from @Document d  
group by d.IDDocument;

with VersionMax as (  
select d.IDDocument, max(d.NumeroVersion) as NumeroVersion, max(d.Score) as ScoreDocument  
from @Document d  
group by d.IDDocument  
)

select top 2 \*  
from VersionMax v  
where v.ScoreDocument \< 1.9  
order by v.ScoreDocument desc

This last query is the one I want to translate to ES syntax :  
-top 2 =\> in docagg / size  
-v.ScoreDocument \< 1.9 =\> term /range filter ?

Any idea on how to do that ?

---

<div class="post-metadata">

**Author:** ![jpountz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jpountz/32/45836_2.png) [@jpountz](https://discuss.elastic.co/u/jpountz)\
**Post date:** [March 27, 2014, 11:47am UTC](https://discuss.elastic.co/t/es-aggregation-and-pagination/16601/4 "2014-03-27T11:47:32Z")

</div>

On Thu, Mar 27, 2014 at 9:45 AM, bob [bkoenig@groupama-ge.fr](mailto:bkoenig@groupama-ge.fr) wrote:

> select top 2 \*  
> from VersionMax v  
> where v.ScoreDocument \< 1.9  
> order by v.ScoreDocument desc
> 
> This last query is the one I want to translate to ES syntax :  
> -top 2 =\> in docagg / size  
> -v.ScoreDocument \< 1.9 =\> term /range filter ?
> 
> Any idea on how to do that ?

You should be able to do that by using a filter aggregation[1] with a  
script filter[2] in order to only run the aggregation on a specific score  
range.

[1]

> **[Elasticsearch Platform — Find real-time answers at scale](https://www.elastic.co)**
>
> Power insights and outcomes with the Elasticsearch Platform and AI. See into your data and find answers that matter with enterprise solutions designed to help you build, observe, and protect. Try Elasticsearch free today.

[2]

> **[Elasticsearch Platform — Find real-time answers at scale](https://www.elastic.co)**
>
> Power insights and outcomes with the Elasticsearch Platform and AI. See into your data and find answers that matter with enterprise solutions designed to help you build, observe, and protect. Try Elasticsearch free today.

--  
Adrien Grand

--  
You received this message because you are subscribed to the Google Groups "elasticsearch" group.  
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CAL6Z4j6nUMbtoTXRAHPt0cRmQyTRW\_NWx0Tgvx-%3DiQmab92fdw%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAL6Z4j6nUMbtoTXRAHPt0cRmQyTRW_NWx0Tgvx-%3DiQmab92fdw%40mail.gmail.com).  
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).

---

<div class="post-metadata">

**Author:** ![bob](https://avatars.discourse-cdn.com/v4/letter/b/ea666f/32.png) [@bob](https://discuss.elastic.co/u/bob)\
**Post date:** [March 27, 2014, 2:07pm UTC](https://discuss.elastic.co/t/es-aggregation-and-pagination/16601/5 "2014-03-27T14:07:18Z")

</div>

Alright, I finally managed to limit the resulting set of documents by adding a size keyword :

{  
"fields":[

],  
"size":0,  
"from":0,  
"sort":[  
"\_score"  
],  
"query":{  
"filtered":{  
"query":{  
"bool":{  
"should":[  
{  
"multi\_match":{  
"query":"general",  
"fields":[  
"Titre.Valeur.original^4",  
"Titre.Valeur.partial",  
"Resume.Valeur.original^2",  
"Resume.Valeur.partial",  
"Corps.Valeur.original^2",  
"Corps.Valeur.partial"  
]  
}  
}  
],  
"minimum\_should\_match":1  
}  
}  
}  
},

```
"aggs" : {

	"Count":{
		 "cardinality":{
			"field":"IDDocument"
		 }
	  },
	  
	  "doc_limit_agg" :{
	  
		"filter" : {
			"script" : {
				"script" : "_doc.score <0.05"
			}
		},
	
		"aggs" : {
			"docagg" : {

				"terms" : {
					"field" : "IDDocument",
					"order" : { "score" : "desc" }
					,"size":4
				},
				"aggs" : {
					"vMax" : { "max" : { "field" : "NumeroVersion" }},
					"score" : { "max" : { "script":"_doc.score"}}
				}
			}
		}
    }
}

```

}

Pagination could then be addressed based on highest score order and its filtered value (see script above).

Last question, and yet maybe something not supported by ES : is there a way to dynamically calculate a column (like a ROW\_NUMBER() in SQL) and use it to sort and filter like above. Actually I'm seeking a better and more robust way to filter/sort than the score column.

Again the SQL equivalent to illustrate what I'm aiming at :

with VersionMax as (  
select d.IDDocument, max(d.NumeroVersion) as NumeroVersion, max(d.Score) as ScoreDocument,  
ROW\_NUMBER() OVER(ORDER BY max(d.Score) DESC, d.IDDocument DESC) as RowNumber  
from @Document d  
group by d.IDDocument  
)  
select top 2 \*  
from VersionMax v  
where v.RowNumber \< 4  
order by v.RowNumber desc

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 6, 2017, 1:40am UTC](https://discuss.elastic.co/t/es-aggregation-and-pagination/16601/6 "2017-07-06T01:40:11Z")

</div>