Elastic search query to filter documents based on the top value of nested array

Question

The document has this structure:

{
   innerArray: [
      {set: "A", value: 123},
      {set: "A", value: 234},
      {set: "B", value: 115},
      {set: "C", value: 133},
      {set: "C", value: 256},
      ...
   ]
}

With a rather complex nested query, I can return documents where innerArray has elements both in set A and with value above a specified limit (e.g. 200). I can also order the inner_hits by value.

This query returns documents that have ANY inner_hits that match the criteria.

{
    "query": {
        "bool": {
            "filter": [
                {
                    "nested": {
                        "path": "innerArray",
                        "inner_hits": { 
                            "sort": { "innerArray.value": { "order": "asc" } }
                        },
                        "query": {
                            "bool": {
                                "filter": [
                                    { "term": { "innerArray.set": "A" }},
                                    { "range": { "innerArray.value" : { "gt": 200 } } }
                                ]
                            }
                        }
                    }
                }
            ]
        }
    }
}

Now, I need only those where the FIRST inner_hit (filtered on 'set' and ordered by 'value') has a value above 200. In pseudo SQL you could use a query with a HAVING clause but you could also write something like this:

select doc
from   documents 
where  (
       select min(value) 
       from   doc.innerArray 
       where  set = 'A'
       ) > 200

Is this feasible in elastic? How can I write such a query?

Answer 1

You would need to use Nested aggregation, a special single bucket aggregation that enables aggregating nested documents.

Query:

GET /_search
{
  "query": {
        "bool": {
            "filter": [
                {
                    "nested": {
                        "path": "innerArray",
                        "inner_hits": { 
                            "sort": { "innerArray.value": { "order": "asc" } }
                        },
                        "query": {
                            "bool": {
                                "filter": [
                                    { "term": { "innerArray.set": "A" }},
                                    { "range": { "innerArray.value" : { "gt": 200 } } }
                                ]
                            }
                        }
                    }
                }
            ]
        }
    }
  },
  "aggs": {
    "resellers": {
      "nested": {
        "path": "innerArray"
      },
      "aggs": {
        "min_value": {
          "min": {
            "field": "innerArray.value"
          }
        }
      }
    }
  }
}