# Show first record of each group

**URL:** <https://discuss.elastic.co/t/show-first-record-of-each-group/319446>\
**Category:** Elasticsearch\
**Created:** [November 21, 2022, 12:28pm UTC](https://discuss.elastic.co/t/show-first-record-of-each-group/319446 "2022-11-21T12:28:00Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![johnnyh](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/johnnyh/32/99291_2.png) [@johnnyh](https://discuss.elastic.co/u/johnnyh)\
**Post date:** [November 21, 2022, 12:28pm UTC](https://discuss.elastic.co/t/show-first-record-of-each-group/319446/1 "2022-11-21T12:28:00Z")

</div>

Hi guys,

I have been trying to find a way perform group by, and then obtaining the first of each group. This set should also be filterable and sort-able, and should also be able to count each group.

Wondering if what I am looking for can be done in Elasticsearch.

For example, given the following dataset:

```auto
{id:"1", group:"A", description:"abc", status:"COMPLETED"},
{id:"2", group:"A", description:"def", status:"PENDING"},
{id:"3", group:"B", description:"ghi", status:"COMPLETED"},
{id:"4", group:"B", description:"jkl", status:"COMPLETED"},
{id:"5", group:"C", description:"mno", status:"COMPLETED"}

```

Is there a way to form a query to obtain something along the lines of:

```auto
{
  ...
  "hits": 3,
  ...
  "_source": [
    {id:"1", group:"A", description:"abc", group_count: 2, group_has_pending: true},
    {id:"3", group:"B", description:"ghi", group_count: 2, group_has_pending: false},
    {id:"5", group:"C", description:"mno", group_count: 1, group_has_pending: false}
  ]
}

```

Searching for "def" should yield results along the line of:

```auto
{
  ...
  "hits": 1,
  ...
  "_source": [
    {id:"2", group:"A", description:"def", group_count: 2, group_has_pending: true},
  ]
}

```

I have tried the `collapse` query but still does not fully satisfy my requirement, i.e. cannot get the total count of documents in a group ignoring filter, and also can't seem to include the `has_pending` into the result set.

The closest example that is similar to what I want to achieve is like how the Gmail web application does it with their search:

 ![filter](https://us1.discourse-cdn.com/elastic/original/3X/0/4/047d8f33386161360dafb22a6d22c45de967b759.jpeg)

(Note that their message count within each conversation group does not change even if filtering is on, and they managed to achieve pagination with the result set as well)

---

<div class="post-metadata">

**Author:** ![johnnyh](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/johnnyh/32/99291_2.png) [@johnnyh](https://discuss.elastic.co/u/johnnyh)\
**Post date:** [December 15, 2022, 7:35am UTC](https://discuss.elastic.co/t/show-first-record-of-each-group/319446/2 "2022-12-15T07:35:32Z")

</div>

Anyone have any idea on how to achieve this?  
Or does anyone implemented similar use cases like the Gmail example?

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [December 15, 2022, 7:41am UTC](https://discuss.elastic.co/t/show-first-record-of-each-group/319446/3 "2022-12-15T07:41:21Z")

</div>

Have you considered using [a transform](https://www.elastic.co/guide/en/elasticsearch/reference/8.5/transform-apis.html) to create a separate index that has one document per group with the data you require?

Elasticsearch does not change the source, so getting the data in the form you specified is as far as I know not possible. You may be able to get just the latest document per group but that could get expensive at scale and does not seem to match your requirement.

---

<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:** [January 12, 2023, 7:41am UTC](https://discuss.elastic.co/t/show-first-record-of-each-group/319446/4 "2023-01-12T07:41:29Z")

</div>

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