# Aggregate count and max per document query

**URL:** <https://discuss.elastic.co/t/aggregate-count-and-max-per-document-query/334583>\
**Category:** Elasticsearch\
**Created:** [May 29, 2023, 12:18pm UTC](https://discuss.elastic.co/t/aggregate-count-and-max-per-document-query/334583 "2023-05-29T12:18:01Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![dimalini](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dimalini/32/22448_2.png) [@dimalini](https://discuss.elastic.co/u/dimalini)\
**Post date:** [May 29, 2023, 12:18pm UTC](https://discuss.elastic.co/t/aggregate-count-and-max-per-document-query/334583/1 "2023-05-29T12:18:01Z")

</div>

Hi, I have a mapping similar to

```auto
PUT my-index-000001
{
  "mappings": {
    "properties": {
      "message": {
        "type": "keyword"
      }
    }
  }
}

```

now this field holds an array of messages. What I would like to request is doc by doc want count of values in this field(an array) and get the doc that has max of this count. Is this query a possibility, pls educate me 🙂 I see individual max or count aggregations but that doesn't fir my case.

TIA!

---

<div class="post-metadata">

**Author:** ![Alexis\_Roberson](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/alexis_roberson/32/113233_2.png) [@Alexis\_Roberson](https://discuss.elastic.co/u/Alexis_Roberson)\
**Post date:** [May 30, 2023, 4:52pm UTC](https://discuss.elastic.co/t/aggregate-count-and-max-per-document-query/334583/2 "2023-05-30T16:52:21Z")

</div>

Hi @dimalini welcome back!

Perhaps you'll find this [solution](https://discuss.elastic.co/t/how-to-get-array-size-for-each-document/226599/2) helpful for your use case. I used it on a sample index similar to the one you mentioned above and it seemed to work fine. Although there are considerations to keep in mind that are mentioned in the solution link.

Documents

```auto
 "hits": {
    "total": {
      "value": 3,
      "relation": "eq"
    },
    "max_score": 1,
    "hits": [
      {
        "_index": "system_logs",
        "_id": "1",
        "_score": 1,
        "_source": {
          "name": "windows",
          "messages": [
            "404",
            "500",
            "200"
          ]
        }
      },
      {
        "_index": "system_logs",
        "_id": "2",
        "_score": 1,
        "_source": {
          "name": "mac",
          "messages": [
            "404",
            "500",
            "200",
            "404",
            "500",
            "200"
          ]
        }
      },
      {
        "_index": "system_logs",
        "_id": "3",
        "_score": 1,
        "_source": {
          "name": "macair",
          "messages": [
            "404",
            "500",
            "200",
            "404",
            "500",
            "200",
            "404",
            "500",
            "200"
          ]
        }
      }
    ]
  }
}

```

Query

```auto
GET system_logs/_search
{
  "_source": {
    "excludes": ["messages"]
  },
  "script_fields": {
    "number_of_messages": {
      "script": {
        "source": "params['_source'].messages.length"
      }
    }
  }
}

```

Result

```auto
"hits": {
    "total": {
      "value": 3,
      "relation": "eq"
    },
    "max_score": 1,
    "hits": [
      {
        "_index": "system_logs",
        "_id": "1",
        "_score": 1,
        "_source": {
          "name": "windows"
        },
        "fields": {
          "number_of_messages": [
            3
          ]
        }
      },
      {
        "_index": "system_logs",
        "_id": "2",
        "_score": 1,
        "_source": {
          "name": "mac"
        },
        "fields": {
          "number_of_messages": [
            6
          ]
        }
      },
      {
        "_index": "system_logs",
        "_id": "3",
        "_score": 1,
        "_source": {
          "name": "macair"
        },
        "fields": {
          "number_of_messages": [
            9
          ]
        }
      }
    ]
  }

```

---

<div class="post-metadata">

**Author:** ![dimalini](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dimalini/32/22448_2.png) [@dimalini](https://discuss.elastic.co/u/dimalini)\
**Post date:** [June 1, 2023, 2:14am UTC](https://discuss.elastic.co/t/aggregate-count-and-max-per-document-query/334583/3 "2023-06-01T02:14:52Z")

</div>

> [@Alexis\_Roberson](#):
>
> ```auto
> "_source": {
> "excludes": ["messages"]
> },
> 
> ```

Thanks Alexis. Always feel great to get timely response from the discuss community.

The script\_fields indeed helped me summarise the count across document. That part is solved with this proposal.

I am planning to try soon, but posting here anyway on the second aspect. That is to use this script\_fields logic and find the docs (top n docs in desc order) that has the max count in them ?

Regards,  
Divya Malini

---

<div class="post-metadata">

**Author:** ![Alexis\_Roberson](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/alexis_roberson/32/113233_2.png) [@Alexis\_Roberson](https://discuss.elastic.co/u/Alexis_Roberson)\
**Post date:** [June 7, 2023, 4:36pm UTC](https://discuss.elastic.co/t/aggregate-count-and-max-per-document-query/334583/4 "2023-06-07T16:36:03Z")

</div>

Hi @dimalini Thank you for your patience. And so glad the first response was helpful. I would say have a look at [bucket sort aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/8.8/search-aggregations-pipeline-bucket-sort-aggregation.html). You'll be able to get the top n documents based on your provided ordering and you can run this with your query.

---

<div class="post-metadata">

**Author:** ![dimalini](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dimalini/32/22448_2.png) [@dimalini](https://discuss.elastic.co/u/dimalini)\
**Post date:** [June 23, 2023, 7:03am UTC](https://discuss.elastic.co/t/aggregate-count-and-max-per-document-query/334583/5 "2023-06-23T07:03:18Z")

</div>

Thanks Alexis. Will give this a try.

---

<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 21, 2023, 7:03am UTC](https://discuss.elastic.co/t/aggregate-count-and-max-per-document-query/334583/6 "2023-07-21T07:03:48Z")

</div>

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