# Elasticsearch Saved Search with Group By

**URL:** <https://discuss.elastic.co/t/elasticsearch-saved-search-with-group-by/257823>\
**Category:** Elasticsearch\
**Created:** [December 7, 2020, 9:42am UTC](https://discuss.elastic.co/t/elasticsearch-saved-search-with-group-by/257823 "2020-12-07T09:42:00Z")\
**Posts on this page:** 17\
**Page:** 1

<div class="post-metadata">

**Author:** ![Kapila](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kapila/32/84671_2.png) [@Kapila](https://discuss.elastic.co/u/Kapila)\
**Post date:** [December 7, 2020, 9:42am UTC](https://discuss.elastic.co/t/elasticsearch-saved-search-with-group-by/257823/1 "2020-12-07T09:42:00Z")

</div>

```auto
index_name: my_data-2020-12-01
ticket_number: T123 
ticket_status: OPEN 
ticket_updated_time: 2020-12-01 12:22:12   

index_name: my_data-2020-12-01 
ticket_number: T124 
ticket_status: OPEN 
ticket_updated_time: 2020-12-01 12:32:11   

index_name: my_data-2020-12-02 
ticket_number: T123 
ticket_status: INPROGRESS 
ticket_updated_time: 2020-12-02 12:33:12   

index_name: my_data-2020-12-02 
ticket_number: T125 
ticket_status: OPEN 
ticket_updated_time: 2020-12-02 14:11:45

```

I want to create a saved search with group by ticket\_number field get unique doc with latest ticket status (ticket\_status). Is it possible?

---

<div class="post-metadata">

**Author:** ![ylasri](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ylasri/32/86120_2.png) [@ylasri](https://discuss.elastic.co/u/ylasri)\
**Post date:** [December 7, 2020, 10:41am UTC](https://discuss.elastic.co/t/elasticsearch-saved-search-with-group-by/257823/2 "2020-12-07T10:41:52Z")

</div>

You can use [top hits aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-top-hits-aggregation.html)

```auto
GET my-logs/_search
{
  "size": 0, 
  "aggs": {
    "tiketId": {
      "terms": {
        "field": "tiketId.keyword",
        "size": 10
      },
      "aggs": {
        "NAME": {
          "top_hits": {
           "sort": [
              {
                "updatedAt": {
                  "order": "desc"
                }
              }
            ],
            "_source": {
              "includes": ["updatedAt", "tiketId", "status"]
            },
            "size": 1
          }
        }
      }
    }
  }
}

```

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/3/d/3d6b36f8d09894a8fd92a9b32b9230b707957112.png)

---

<div class="post-metadata">

**Author:** ![Kapila](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kapila/32/84671_2.png) [@Kapila](https://discuss.elastic.co/u/Kapila)\
**Post date:** [December 7, 2020, 11:21am UTC](https://discuss.elastic.co/t/elasticsearch-saved-search-with-group-by/257823/3 "2020-12-07T11:21:35Z")

</div>

How to apply this in saved search?

---

<div class="post-metadata">

**Author:** ![ylasri](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ylasri/32/86120_2.png) [@ylasri](https://discuss.elastic.co/u/ylasri)\
**Post date:** [December 7, 2020, 11:32am UTC](https://discuss.elastic.co/t/elasticsearch-saved-search-with-group-by/257823/4 "2020-12-07T11:32:18Z")

</div>

This is an aggregation,  
If you want to apply same logic in search, you can use [collapse](https://www.elastic.co/guide/en/elasticsearch/reference/current/collapse-search-results.html)

```auto
GET my-logs/_search
{
  "query": {
    "match_all": {}
  },
  "collapse": {
    "field": "tiketId.keyword"
  },
  "sort": [
    {
      "updatedAt": {
        "order": "desc"
      }
    }
  ]
}

```

---

<div class="post-metadata">

**Author:** ![Kapila](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kapila/32/84671_2.png) [@Kapila](https://discuss.elastic.co/u/Kapila)\
**Post date:** [December 7, 2020, 11:55am UTC](https://discuss.elastic.co/t/elasticsearch-saved-search-with-group-by/257823/5 "2020-12-07T11:55:30Z")

</div>

> [@ylasri](#):
>
> ```auto
> GET my-logs/_search
> {
> "query": {
> "match_all": {}
> },
> "collapse": {
> "field": "tiketId.keyword"
> },
> "sort": [
> {
> "updatedAt": {
> "order": "desc"
> }
> }
> ]
> }
> 
> ```

How can I create visualizations using this?

---

<div class="post-metadata">

**Author:** ![ylasri](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ylasri/32/86120_2.png) [@ylasri](https://discuss.elastic.co/u/ylasri)\
**Post date:** [December 7, 2020, 11:57am UTC](https://discuss.elastic.co/t/elasticsearch-saved-search-with-group-by/257823/6 "2020-12-07T11:57:07Z")

</div>

Check my first answer,  
To create such visualisation, use the top hits as a metric

---

<div class="post-metadata">

**Author:** ![Kapila](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kapila/32/84671_2.png) [@Kapila](https://discuss.elastic.co/u/Kapila)\
**Post date:** [December 7, 2020, 11:59am UTC](https://discuss.elastic.co/t/elasticsearch-saved-search-with-group-by/257823/7 "2020-12-07T11:59:41Z")

</div>

Yes, I tried. Tried to create a Pie chart but seems top hits allow only numeric fields.

Any workaround?

---

<div class="post-metadata">

**Author:** ![ylasri](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ylasri/32/86120_2.png) [@ylasri](https://discuss.elastic.co/u/ylasri)\
**Post date:** [December 7, 2020, 2:14pm UTC](https://discuss.elastic.co/t/elasticsearch-saved-search-with-group-by/257823/8 "2020-12-07T14:14:17Z")

</div>

That will not be possible with Pie 🙂  
I suggest to have a separated index where you maintain the latest status & update date.  
How your data is ingested into elasticsearch ?

---

<div class="post-metadata">

**Author:** ![Kapila](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kapila/32/84671_2.png) [@Kapila](https://discuss.elastic.co/u/Kapila)\
**Post date:** [December 8, 2020, 3:04pm UTC](https://discuss.elastic.co/t/elasticsearch-saved-search-with-group-by/257823/9 "2020-12-08T15:04:43Z")

</div>

Through a Kafka topic

---

<div class="post-metadata">

**Author:** ![ylasri](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ylasri/32/86120_2.png) [@ylasri](https://discuss.elastic.co/u/ylasri)\
**Post date:** [December 8, 2020, 3:08pm UTC](https://discuss.elastic.co/t/elasticsearch-saved-search-with-group-by/257823/10 "2020-12-08T15:08:55Z")

</div>

Means you are using any logstash pipeline to sync data into elasticsearch ?  
If yes then you can send data into 2 elasticsearch indexes

- 1 as uou are doing to keep track of each change on you ticket
- Seconday index with document\_id =\> %{Ticket\_id}, so you will override the exiting document with the latest update

---

<div class="post-metadata">

**Author:** ![Kapila](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kapila/32/84671_2.png) [@Kapila](https://discuss.elastic.co/u/Kapila)\
**Post date:** [December 8, 2020, 3:40pm UTC](https://discuss.elastic.co/t/elasticsearch-saved-search-with-group-by/257823/11 "2020-12-08T15:40:10Z")

</div>

Yes, that's what exactly what we are doing but the issue is, since we are creating indices daily, the document is not getting updated in the second index after the created date.

Either it should update the existing document or create new document in new date index but nothing is happening.

---

<div class="post-metadata">

**Author:** ![ylasri](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ylasri/32/86120_2.png) [@ylasri](https://discuss.elastic.co/u/ylasri)\
**Post date:** [December 8, 2020, 3:45pm UTC](https://discuss.elastic.co/t/elasticsearch-saved-search-with-group-by/257823/12 "2020-12-08T15:45:08Z")

</div>

Oops i see the point 🙂  
I would suggest to use tranform, but it does not support yet the top hit aggs

---

<div class="post-metadata">

**Author:** ![ylasri](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ylasri/32/86120_2.png) [@ylasri](https://discuss.elastic.co/u/ylasri)\
**Post date:** [December 8, 2020, 3:49pm UTC](https://discuss.elastic.co/t/elasticsearch-saved-search-with-group-by/257823/13 "2020-12-08T15:49:23Z")

</div>

How big is your dataset to use a daily index for tickets ?

---

<div class="post-metadata">

**Author:** ![Kapila](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kapila/32/84671_2.png) [@Kapila](https://discuss.elastic.co/u/Kapila)\
**Post date:** [December 8, 2020, 5:02pm UTC](https://discuss.elastic.co/t/elasticsearch-saved-search-with-group-by/257823/14 "2020-12-08T17:02:26Z")

</div>

Size of a daily index is between 1gb and 1.5 gb.

Unfortunately we don't have much control to change index creation policy (I meant daily index creation) as we are working on client environment.

Also, tried to create indices based on ticket created date but we don't get created time in every payload.

Thank you very much for your support, highly appreciated!

---

<div class="post-metadata">

**Author:** ![ylasri](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ylasri/32/86120_2.png) [@ylasri](https://discuss.elastic.co/u/ylasri)\
**Post date:** [December 14, 2020, 11:38am UTC](https://discuss.elastic.co/t/elasticsearch-saved-search-with-group-by/257823/15 "2020-12-14T11:38:49Z")

</div>

Hi @Kapila  
Here is an [example](https://www.elastic.co/guide/en/elasticsearch/reference/7.10/transform-painless-examples.html#painless-top-hits) on how to use a scripted aggs to get the top hits inside a tranform, this will definelty help you with your use case

---

<div class="post-metadata">

**Author:** ![ylasri](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ylasri/32/86120_2.png) [@ylasri](https://discuss.elastic.co/u/ylasri)\
**Post date:** [December 14, 2020, 11:47am UTC](https://discuss.elastic.co/t/elasticsearch-saved-search-with-group-by/257823/16 "2020-12-14T11:47:36Z")

</div>

Here is an example

```auto
PUT _transform/tickets_tranform
{
  "source": {
    "index": [
      "tickets"
    ]
  },
  "pivot": {
    "group_by": {
      "ticket_id.keyword": {
        "terms": {
          "field": "ticket_id.keyword"
        }
      }
    },
    "aggregations": {
      "latest_doc": {
        "scripted_metric": {
          "init_script": "state.timestamp_latest = 0L; state.last_doc = ''",
          "map_script": """ 
        def current_date = doc['updated_at'].getValue().toInstant().toEpochMilli();
        if (current_date > state.timestamp_latest)
        {state.timestamp_latest = current_date;
        state.last_doc = new HashMap(params['_source']);}
      """,
          "combine_script": "return state",
          "reduce_script": """ 
        def last_doc = '';
        def timestamp_latest = 0L;
        for (s in states) {if (s.timestamp_latest > (timestamp_latest))
        {timestamp_latest = s.timestamp_latest; last_doc = s.last_doc;}}
        return last_doc
      """
        }
      }
    }
  },
  "description": "Get the latest update per ticket_id",
  "dest": {
    "index": "tickets_tranform"
  },
  "frequency": "5m",
  "sync": {
    "time": {
      "field": "updated_at",
      "delay": "60s"
    }
  }
}

```

---

<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 11, 2021, 11:47am UTC](https://discuss.elastic.co/t/elasticsearch-saved-search-with-group-by/257823/17 "2021-01-11T11:47:39Z")

</div>

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