How to sort an elasticsearch query based on the size of nested objects that match a condition?

[EmilieM (Emilie)]

I try to sort my elastic search query based on the size of nested objects that match a condition (here: size of boosters with a specific status)

Here is my mapping :

{
  "companies" : {
    "mappings" : {
      "dynamic" : "false",
      "properties" : {
        "authorization_level" : { "type" : "keyword" },
        "boosters" : {
          "type" : "nested",
          "properties" : {
            "id" : { "type" : "keyword" },
            "status" : { "type" : "keyword" } //condition is on this specific field
          }
        },
        /// More properties not relevant here
      }
    }
  }
}

If I simplify my query to keep only the sorting part, I have this :

GET companies/_search
{ 
  "sort": [
    { "authorization_level": { "order": "asc" } },
    { "_script": 
      { 
        "script": "params['_source']['boosters'].size()", 
        "order": "desc", 
        "type": "number" 
      } 
    }
  ]
}

The script part is working well to sort companies by size of the nested field boosters . But now I want to sort it by the size of boosters that have a specific status (eg: booster can have 2 different status completed or pending , and I want to count only the pending booster).

Would you know how to write the script part in order to achieve this ? Painless scripting language is not painless at all for me...

Thanks a lot

[spinscale (Alexander Reelsen)]

Hey,

you may need to switch your mental model a little and consider adding this information on index time. If you have a limited number of statuses, you could add an ingest pipeline, that counts each status and indexes it.

However this is still not the same as if you only want to count based on what actually matched your query.

If you take a step back and explain your use-case maybe someone has a smarter idea with a different data model...

--Alex