Is there a better way to perform this type of query?

[Kevin_Bourne]

I have come up with a way to perform 2 separate queries to get the data that I want but I was wondering is there a better way, specifically if there is a way I can do this query in a single query.

Here is example data with only 3 fields from a larger document:

id parentId status

1 100 success
2 100 success
3 100 error
4 200 partial
5 300 error
6 300 success

What I ultimately want is the full document of the latest object for each parentId that has a list of specific statuses. The latest object is the one with that has the largest id for each parentId. So in the above if I only wanted the latest objects with a status of success OR partial then I would want the full documents for ids 4 and 6.

My current solution is to do a terms query on parentId, then a max query on the id field to get the ids, which using the above would give me ids 3, 4 and 6. Then I will make a second query to retrieve only those ids but than add a filter to only retrieve where status is success OR partial to return the documents for ids 4 and 6. Is there a way to do this in a single query?

For completion, here is the aggregation I am doing to get the first set of ids, which does work:

"aggs": {
    "parentIdAgg": {
        "terms": {
            "field": "parentId"
        },
        "aggs": {
            "idAdd": {
                "max": {
                    "field": "id"
                }
            }
        }
    }
}