# Get first N values, then aggregate

**URL:** <https://discuss.elastic.co/t/get-first-n-values-then-aggregate/151950>\
**Category:** Elasticsearch\
**Created:** [October 10, 2018, 11:05pm UTC](https://discuss.elastic.co/t/get-first-n-values-then-aggregate/151950 "2018-10-10T23:05:08Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![moisesjbc](https://avatars.discourse-cdn.com/v4/letter/m/b4bc9f/32.png) [@moisesjbc](https://discuss.elastic.co/u/moisesjbc)\
**Post date:** [October 10, 2018, 11:05pm UTC](https://discuss.elastic.co/t/get-first-n-values-then-aggregate/151950/1 "2018-10-10T23:05:08Z")

</div>

Good night

I have test documents like the following:

PUT /megacorp/employees/1  
{  
"text": "text1",  
"number": 1  
}

PUT /megacorp/employees/2  
{  
"text": "text1",  
"number": 2  
}

PUT /megacorp/employees/3  
{  
"text": "text2",  
"number": 3  
}

PUT /megacorp/employees/4  
{  
"text": "text2",  
"number": 4  
}

PUT /megacorp/employees/5  
{  
"text": "text2",  
"number": 5  
}

I would like to:

1. Query for all documents matching a certain criteria (ie. "number" \> 0)
2. Sort results (ie. "number" ASC)
3. Take the first N results (ie. N=3)
4. Then aggregate (ie. get the maximum value "number").

I have tried using limit filter, "size" parameter, filter agg... but the result is always the same: although hits array displays 3 elements, both "total" and "max agg" values are 5. How can I limit results then aggregate only the N first results?

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [October 10, 2018, 11:18pm UTC](https://discuss.elastic.co/t/get-first-n-values-then-aggregate/151950/2 "2018-10-10T23:18:10Z")

</div>

What did you do so far?

---

<div class="post-metadata">

**Author:** ![moisesjbc](https://avatars.discourse-cdn.com/v4/letter/m/b4bc9f/32.png) [@moisesjbc](https://discuss.elastic.co/u/moisesjbc)\
**Post date:** [October 11, 2018, 7:30am UTC](https://discuss.elastic.co/t/get-first-n-values-then-aggregate/151950/3 "2018-10-11T07:30:23Z")

</div>

Hi dadoonet, thank you for asking!

First thing: I forgot to mention that we are using ElasticSearch 2.4.5 (I know, such an old version).

My attempts so far (In this simple case I want to take only 3 first ordered hits and aggregate those so the max-number agg return 3 instead of 5 (the maximum of all documents)

(1) Original query:

```
GET /megacorp/_search
{
  "query": {
    "filtered": {
      "filter": {
        "range" : {
          "number": {
            "gte": 0
          }
        }
      },
      "query": {
        "match_all": {}
      }
    }
  },
  "aggs": {
    "max-number": {"max": {"field": "number"}}
  },
  "sort": [
      { "number" : {"order" : "asc"}}
    ]
}

```

(2) Original query - result

```
{
  "took": 8,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "failed": 0
  },
  "hits": {
    "total": 5,
    "max_score": null,
    "hits": [
      {
        "_index": "megacorp",
        "_type": "employees",
        "_id": "1",
        "_score": null,
        "_source": {
          "text": "text1",
          "number": 1
        },
        "sort": [
          1
        ]
      },
      {
        "_index": "megacorp",
        "_type": "employees",
        "_id": "2",
        "_score": null,
        "_source": {
          "text": "text1",
          "number": 2
        },
        "sort": [
          2
        ]
      },
      {
        "_index": "megacorp",
        "_type": "employees",
        "_id": "3",
        "_score": null,
        "_source": {
          "text": "text2",
          "number": 3
        },
        "sort": [
          3
        ]
      },
      {
        "_index": "megacorp",
        "_type": "employees",
        "_id": "4",
        "_score": null,
        "_source": {
          "text": "text2",
          "number": 4
        },
        "sort": [
          4
        ]
      },
      {
        "_index": "megacorp",
        "_type": "employees",
        "_id": "5",
        "_score": null,
        "_source": {
          "text": "text2",
          "number": 5
        },
        "sort": [
          5
        ]
      }
    ]
  },
  "aggregations": {
    "max-number": {
      "value": 5
    }
  }
}

```

(2) Limit filter in aggregation (if I change the limit filter with another condition - ie. number \< 3 - I get max = 5, but I need to limit results by number of hits)

```
GET /megacorp/_search
{
  "query": {
    "filtered": {
      "filter": {
        "range" : {
          "number": {
            "gte": 0
          }
        }
      },
      "query": {
        "match_all": {}
      }
    }
  },
  "aggs": {
    "foo": {
      "filter": {"limit" : {"value" : 2}},
      "aggs": {
        "max-number": {"max": {"field": "number"}}
      }
    }
  },
  "sort": [
      { "number" : {"order" : "asc"}}
    ]
}

```

(2) Limit filter in aggregation - result:

```
{
  "took": 13,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "failed": 0
  },
  "hits": {
    "total": 5,
    "max_score": null,
    "hits": [
      {
        "_index": "megacorp",
        "_type": "employees",
        "_id": "1",
        "_score": null,
        "_source": {
          "text": "text1",
          "number": 1
        },
        "sort": [
          1
        ]
      },
      {
        "_index": "megacorp",
        "_type": "employees",
        "_id": "2",
        "_score": null,
        "_source": {
          "text": "text1",
          "number": 2
        },
        "sort": [
          2
        ]
      },
      {
        "_index": "megacorp",
        "_type": "employees",
        "_id": "3",
        "_score": null,
        "_source": {
          "text": "text2",
          "number": 3
        },
        "sort": [
          3
        ]
      },
      {
        "_index": "megacorp",
        "_type": "employees",
        "_id": "4",
        "_score": null,
        "_source": {
          "text": "text2",
          "number": 4
        },
        "sort": [
          4
        ]
      },
      {
        "_index": "megacorp",
        "_type": "employees",
        "_id": "5",
        "_score": null,
        "_source": {
          "text": "text2",
          "number": 5
        },
        "sort": [
          5
        ]
      }
    ]
  },
  "aggregations": {
    "foo": {
      "doc_count": 5,
      "max-number": {
        "value": 5
      }
    }
  }
}
```

---

<div class="post-metadata">

**Author:** ![moisesjbc](https://avatars.discourse-cdn.com/v4/letter/m/b4bc9f/32.png) [@moisesjbc](https://discuss.elastic.co/u/moisesjbc)\
**Post date:** [October 11, 2018, 7:30am UTC](https://discuss.elastic.co/t/get-first-n-values-then-aggregate/151950/4 "2018-10-11T07:30:56Z")

</div>

(3) Sampler aggregation

```
GET /megacorp/_search
{
  "query": {
    "filtered": {
      "filter": {
        "range" : {
          "number": {
            "gte": 0
          }
        }
      },
      "query": {
        "match_all": {}
      }
    }
  },
  "aggs": {
     "bestDocs": {
         "sampler": {
              "shard_size": 3
         },
         "aggs": {
                "max_number" : { "max" : { "field" : "number" } }
         }
      }
  },
  "sort": [
        { "number" : {"order" : "desc"}}
      ]
}

```

(3) Sampler aggregation - result:

```
{
  "took": 13,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "failed": 0
  },
  "hits": {
    "total": 5,
    "max_score": null,
    "hits": [
      {
        "_index": "megacorp",
        "_type": "employees",
        "_id": "5",
        "_score": null,
        "_source": {
          "text": "text2",
          "number": 5
        },
        "sort": [
          5
        ]
      },
      {
        "_index": "megacorp",
        "_type": "employees",
        "_id": "4",
        "_score": null,
        "_source": {
          "text": "text2",
          "number": 4
        },
        "sort": [
          4
        ]
      },
      {
        "_index": "megacorp",
        "_type": "employees",
        "_id": "3",
        "_score": null,
        "_source": {
          "text": "text2",
          "number": 3
        },
        "sort": [
          3
        ]
      },
      {
        "_index": "megacorp",
        "_type": "employees",
        "_id": "2",
        "_score": null,
        "_source": {
          "text": "text1",
          "number": 2
        },
        "sort": [
          2
        ]
      },
      {
        "_index": "megacorp",
        "_type": "employees",
        "_id": "1",
        "_score": null,
        "_source": {
          "text": "text1",
          "number": 1
        },
        "sort": [
          1
        ]
      }
    ]
  },
  "aggregations": {
    "bestDocs": {
      "doc_count": 5,
      "max_number": {
        "value": 5
      }
    }
  }
}
```

---

<div class="post-metadata">

**Author:** ![moisesjbc](https://avatars.discourse-cdn.com/v4/letter/m/b4bc9f/32.png) [@moisesjbc](https://discuss.elastic.co/u/moisesjbc)\
**Post date:** [October 11, 2018, 7:31am UTC](https://discuss.elastic.co/t/get-first-n-values-then-aggregate/151950/5 "2018-10-11T07:31:30Z")

</div>

(4) Terminate after + size (query params)

```
POST /megacorp/_search?terminate_after=3&size=3
{
  "query": {
    "match_all": {}
  },
  "sort": [
      { "number" : {"order" : "asc"}}
    ],
  "aggs": {
    "max-number": {
      "max": {"field": "number"}
    }
  }
}

```

(4) Terminate after + size (query params) - result:

```
{
  "took": 19,
  "timed_out": false,
  "terminated_early": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "failed": 0
  },
  "hits": {
    "total": 5,
    "max_score": null,
    "hits": [
      {
        "_index": "megacorp",
        "_type": "employees",
        "_id": "1",
        "_score": null,
        "_source": {
          "text": "text1",
          "number": 1
        },
        "sort": [
          1
        ]
      },
      {
        "_index": "megacorp",
        "_type": "employees",
        "_id": "2",
        "_score": null,
        "_source": {
          "text": "text1",
          "number": 2
        },
        "sort": [
          2
        ]
      },
      {
        "_index": "megacorp",
        "_type": "employees",
        "_id": "3",
        "_score": null,
        "_source": {
          "text": "text2",
          "number": 3
        },
        "sort": [
          3
        ]
      }
    ]
  },
  "aggregations": {
    "max-number": {
      "value": 5
    }
  }
}

```

(5) Size in body

```
POST /megacorp/_search
{
  "size": 3,
  "query": {
    "match_all": {}
  },
  "aggs": {
    "max-number": {
      "max": {"field": "number"}
    }
  }
}

```

(5) Size in body - result

```
{
  "took": 6,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "failed": 0
  },
  "hits": {
    "total": 5,
    "max_score": 1,
    "hits": [
      {
        "_index": "megacorp",
        "_type": "employees",
        "_id": "5",
        "_score": 1,
        "_source": {
          "text": "text2",
          "number": 5
        }
      },
      {
        "_index": "megacorp",
        "_type": "employees",
        "_id": "2",
        "_score": 1,
        "_source": {
          "text": "text1",
          "number": 2
        }
      },
      {
        "_index": "megacorp",
        "_type": "employees",
        "_id": "4",
        "_score": 1,
        "_source": {
          "text": "text2",
          "number": 4
        }
      }
    ]
  },
  "aggregations": {
    "max-number": {
      "value": 5
    }
  }
}

```

Thank you!

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [October 12, 2018, 3:49pm UTC](https://discuss.elastic.co/t/get-first-n-values-then-aggregate/151950/6 "2018-10-12T15:49:56Z")

</div>

Why does the 1st query is not correct in you case?

Do you mean that you want to compute aggregations only on top of a part of the resultset but not the whole resultset?

---

<div class="post-metadata">

**Author:** ![moisesjbc](https://avatars.discourse-cdn.com/v4/letter/m/b4bc9f/32.png) [@moisesjbc](https://discuss.elastic.co/u/moisesjbc)\
**Post date:** [October 12, 2018, 9:29pm UTC](https://discuss.elastic.co/t/get-first-n-values-then-aggregate/151950/7 "2018-10-12T21:29:30Z")

</div>

Yes, I want to add a limit to the first query, so first I get the N first hits from the resultset and then apply a aggregation on them.

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [October 12, 2018, 11:34pm UTC](https://discuss.elastic.co/t/get-first-n-values-then-aggregate/151950/8 "2018-10-12T23:34:08Z")

</div>

I don't know how to do this apart doing that on client side as this looks super easy to solve on this side.

---

<div class="post-metadata">

**Author:** ![moisesjbc](https://avatars.discourse-cdn.com/v4/letter/m/b4bc9f/32.png) [@moisesjbc](https://discuss.elastic.co/u/moisesjbc)\
**Post date:** [October 15, 2018, 1:25pm UTC](https://discuss.elastic.co/t/get-first-n-values-then-aggregate/151950/9 "2018-10-15T13:25:37Z")

</div>

Hi dadoonet

We indeed ended doing a little workaround on client side.

Thank you!

---

<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:** [November 12, 2018, 1:38pm UTC](https://discuss.elastic.co/t/get-first-n-values-then-aggregate/151950/10 "2018-11-12T13:38:47Z")

</div>

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