# In elasticsearch finding documents which meet a specific criteria for the latest for each group

**URL:** https://discuss.elastic.co/t/in-elasticsearch-finding-documents-which-meet-a-specific-criteria-for-the-latest-for-each-group/329023
**Category:** Elasticsearch
**Created:** [March 31, 2023, 8:11am UTC](https://discuss.elastic.co/t/in-elasticsearch-finding-documents-which-meet-a-specific-criteria-for-the-latest-for-each-group/329023 "2023-03-31T08:11:42Z")
**Posts on this page:** 4
**Page:** 1

<div class="post-metadata">

### Author: ![m.a.tanaka](https://avatars.discourse-cdn.com/v4/letter/m/34f0e0/32.png) [@m.a.tanaka](https://discuss.elastic.co/u/m.a.tanaka)
#### Post date: [March 31, 2023, 8:11am UTC](https://discuss.elastic.co/t/in-elasticsearch-finding-documents-which-meet-a-specific-criteria-for-the-latest-for-each-group/329023/1 "2023-03-31T08:11:42Z")

</div>

I am trying to create a query in elasticsearch, which is able to retrieve the documents for each group, which is the latest document within each group and meet a specific criteria. But I have not been able to solve this one.

Say following documents are indexed in myindex in elasticsearch:

```auto
POST /myindex/_bulk
{ "index":{} }
{ "objid": 1, "ident":"group1","version":1, "chdate": 1, "field1" : 1}
{ "index":{} }
{ "objid": 2, "ident":"group1","version":2, "chdate": 2, "field1" : 0}
{ "index":{} }
{ "objid": 3, "ident":"group1","version":2, "chdate": 3, "field1" : 1}
{ "index":{} }
{ "objid": 4, "ident":"group1","version":2, "chdate": 4, "field1" : 0}
{ "index":{} }
{ "objid": 5, "ident":"group1","version":3, "chdate": 1, "field1" : 0}

```

I would like to find all documents, which has field1 set to x if the document with the highest chdate, for each ident and version, which has field1 set to x.

In a case where x is 0 then the documents, which has objid 4 and 5 should be returned In a case where x is 1 then the documents, which has objid 1 should be returned

```auto
{
  "size": 0,
  "aggs": {
    "by_ident": {
      "terms": {
        "field": "ident.keyword",
        "size": 10
      },
      "aggs": {
        "by_version": {
          "terms": {
            "field": "version",
            "size": 10000
          },
          "aggs": {
            "by_latest": {
              "top_hits": {
                "sort": [{
                  "chdate": {
                    "order": "desc"
                  }
                }], 
                "size": 1
              }
            }
          }
        }
      }
    }
  }
}

```

But I am unsure how I can add the criteria that field1 should be equal to x

Thanks in advance

---

<div class="post-metadata">

### Author: ![RabBit\_BR](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rabbit_br/32/82261_2.png) [@RabBit\_BR](https://discuss.elastic.co/u/RabBit_BR)
#### Post date: [March 31, 2023, 11:06am UTC](https://discuss.elastic.co/t/in-elasticsearch-finding-documents-which-meet-a-specific-criteria-for-the-latest-for-each-group/329023/2 "2023-03-31T11:06:17Z")

</div>

Hi @m.a.tanaka

You can add the [term query](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-terms-query.html) for [filter](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-filter-context.html#filter-context) documents with field1 equals 1 after aggs like this:

```auto
{
  "size": 0,
  "query": {
    "bool": {
      "filter": [
        {
          "term": {
            "field1": 1
          }
        }
      ]
    }
  }, 
  "aggs": {
    "by_ident": {
      "terms": {
        "field": "ident.keyword",
        "size": 10
      },
      "aggs": {
        "by_version": {
          "terms": {
            "field": "version",
            "size": 10000
          },
          "aggs": {
            "by_latest": {
              "top_hits": {
                "sort": [{
                  "chdate": {
                    "order": "desc"
                  }
                }], 
                "size": 1
              }
            }
          }
        }
      }
    }
  }
}

```

You can use a query without aggs. This example use [Sort](https://www.elastic.co/guide/en/elasticsearch/reference/current/sort-search-results.html).

```auto
{
  "size": 10,
  "query": {
    "bool": {
      "filter": [
        {
          "term": {
            "field1": 0
          }
        }
      ]
    }
  },
  "sort": [
    {
      "chdate": {
        "order": "desc"
      }
    }
  ]
}

```

---

<div class="post-metadata">

### Author: ![m.a.tanaka](https://avatars.discourse-cdn.com/v4/letter/m/34f0e0/32.png) [@m.a.tanaka](https://discuss.elastic.co/u/m.a.tanaka)
#### Post date: [March 31, 2023, 5:17pm UTC](https://discuss.elastic.co/t/in-elasticsearch-finding-documents-which-meet-a-specific-criteria-for-the-latest-for-each-group/329023/3 "2023-03-31T17:17:38Z")

</div>

Unfortunately it will not work because it will pick up the documents which has objid 1 and 3, however it should not pick up the one that has objid 3, as for that version (i.e. version 2) it should look at the document with objid 4 and not pick it up as that document has field1 set to 0

---

<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: [April 28, 2023, 5:18pm UTC](https://discuss.elastic.co/t/in-elasticsearch-finding-documents-which-meet-a-specific-criteria-for-the-latest-for-each-group/329023/4 "2023-04-28T17:18:09Z")

</div>

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