Can you filter on top ranked inner nested hit in same query

[chimauwah]

Cheers all!

Let's say I have a mapping with a nested object like this:

{
  "APP_APPLICATION_ID": {
    "type": "long"
  },
  "APP_FNAME": {
    "type": "text",
    "fields": {
      "raw": {
        "type": "keyword"
      }
    }
  },
  "PROGRAM_ASSIGNMENT_DATA": {
    "type": "nested",
    "properties": {
      "PA_SEQ_NBR": {
        "type": "long"
      },
      "PA_DOC_ID": {
        "type": "long"
      },
      "PA_DOC_RELEASE_DT": {
        "type": "date"
      }
    }
  }
}

What I want to do is first sort the inner hits of every top-level document, then apply a filter to ONLY the top ranked (or index 0-however you want to look at it) inner hit document.

For example, what does the query need to look like to return all top-level documents whose most "recent" document, has a PA_DOC_ID = 19, where most recent is defined by sorting PA_DOC_RELASE_DT desc then PA_SEQ_NBR asc?

A sample document might look like this:

{
  "APP_APPLICATION_ID": 100,
  "APP_FNAME": "Clark",
  "PROGRAM_ASSIGNMENT_DATA": [
    {
      "PA_SEQ_NBR": 1,
      "PA_DOC_ID": 19,
      "PA_DOC_RELEASE_DT" : "2022-04-30T00:00:00Z",
    },
    {
      "PA_SEQ_NBR": 2,
      "PA_DOC_ID": 19,
      "PA_DOC_RELEASE_DT" : "2022-04-30T00:00:00Z",
    },
    {
      "PA_SEQ_NBR": 3,
      "PA_DOC_ID": 19,
      "PA_DOC_RELEASE_DT" : "2022-04-30T00:00:00Z",
    },
    {
      "PA_SEQ_NBR": 4,
      "PA_DOC_ID": 9,
      "PA_DOC_RELEASE_DT" : "2022-05-22T00:00:00Z",
    },
    {
      "PA_SEQ_NBR": 5,
      "PA_DOC_ID": 13,
      "PA_DOC_RELEASE_DT" : "2022-05-22T00:00:00Z",
    }
  ]
}

It's highest ranked inner hit would be PA_SEQ_NBR:4. Then applying the filter, this inner hit document does NOT match PA_DOC_ID=19, so this document would NOT return.

I would like to do this all in 1 query.

Is it possible to do this all in the same query? If not, which parts can and cannot be done?

Any guidance would be much appreciated. Thanks!