# Find top 10 documents when 1 document matches criteria

**URL:** <https://discuss.elastic.co/t/find-top-10-documents-when-1-document-matches-criteria/206696>\
**Category:** Elasticsearch\
**Created:** [November 6, 2019, 12:52am UTC](https://discuss.elastic.co/t/find-top-10-documents-when-1-document-matches-criteria/206696 "2019-11-06T00:52:10Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![kidrik](https://avatars.discourse-cdn.com/v4/letter/k/85f322/32.png) [@kidrik](https://discuss.elastic.co/u/kidrik)\
**Post date:** [November 6, 2019, 12:52am UTC](https://discuss.elastic.co/t/find-top-10-documents-when-1-document-matches-criteria/206696/1 "2019-11-06T00:52:10Z")

</div>

I have an index with over 30M documents. Every document has a parentId field and a createdOn field. Modified documents have a modifiedOn field. Dates are stored as epoch\_millis. I need to find the top 10 most-recent documents per parentId if at least one of the "child" documents has been created/modified within a date range. I tried using a composite aggregation, but it will take way too long to complete.

```
{
    "aggregations": {
        "lists": {
            "composite": {
                "sources": [
                    {
                        "parentId": {
                            "terms": {
                                "field": "parentId"
                            }
                        }
                    }
                ],
                "size": 100
            },
            "aggregations": {
                "most_recent": {
                    "max": {
                        "script": {
                            "source": "doc['modifiedOn'].size() == 0 || doc['modifiedOn'].value.millis == null ? doc['createdOn'].value.millis : doc['modifiedOn'].value.millis"
                        }
                    }
                },
                "most_recent_filter": {
                    "bucket_selector": {
                        "buckets_path": {
                            "modified_on": "most_recent"
                        },
                        "script": "params.modified_on >= 1552003847225L"
                    }
                },
                "top_items": {
                    "top_hits": {
                        "_source": [                            
                            "parentId",
                            "otherFields",                 
                            "createdOn",
                            "modifiedOn"
                        ],
                        "sort": {
                            "_script": {
                                "type": "number",
                                "script": {
                                    "source": "doc['modifiedOn'].size() == 0 || doc['modifiedOn'].value.millis == null ? doc['createdOn'].value.millis : doc['modifiedOn'].value.millis"
                                },
                                "order": "desc",
                                "mode": "max"
                            }
                        },
                        "size": 10
                    }
                }
            }
        }
    },
    "size": 0
}

```

Is there a better way to do this?

---

<div class="post-metadata">

**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\
**Post date:** [November 6, 2019, 9:19am UTC](https://discuss.elastic.co/t/find-top-10-documents-when-1-document-matches-criteria/206696/2 "2019-11-06T09:19:30Z")

</div>

Hey,

not sure if this works with your data, but you could try using [field collapsing](https://www.elastic.co/guide/en/elasticsearch/reference/7.4/search-request-body.html#request-body-search-collapse) and collapse on the parentId field and then sort by modifiedOn and add a date range filter in the query.

--Alex

---

<div class="post-metadata">

**Author:** ![kidrik](https://avatars.discourse-cdn.com/v4/letter/k/85f322/32.png) [@kidrik](https://discuss.elastic.co/u/kidrik)\
**Post date:** [November 6, 2019, 8:20pm UTC](https://discuss.elastic.co/t/find-top-10-documents-when-1-document-matches-criteria/206696/3 "2019-11-06T20:20:13Z")

</div>

How would I handle multiple requests? I need to cover 30M documents and there's a limit on from + size with field collapsing of 10k. Collapse also cannot be used with scroll or search\_after. Is there a way?

---

<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:** [December 4, 2019, 8:20pm UTC](https://discuss.elastic.co/t/find-top-10-documents-when-1-document-matches-criteria/206696/4 "2019-12-04T20:20:18Z")

</div>

Closed on December 4, 2019, 8:20pm UTC

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