Sort Parent Documents based on Inner_Hits result object field

[arunjp]

I use ElasticSearch 6.3.2. I have Parent/Child relationship with my document model. I am trying to sort the Parent documents by child document's field determined by inner_hits.

This is the mapping:
{
"offer_index" : {
"aliases" : { },
"mappings" : {
"doc" : {
"_routing" : {
"required" : true
},
"properties" : {
"couponType" : {
"type" : "integer"
},
"cusip" : {
"type" : "text",
"fielddata" : true
},
"id" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"isDeleted" : {
"type" : "boolean"
},
"maxQuantity" : {
"type" : "integer"
},
"minQuantity" : {
"type" : "integer"
},
"myJoinField" : {
"type" : "join",
"eager_global_ordinals" : true,
"relations" : {
"parent" : [
"offer",
"bidwanted"
]
}
},
"price" : {
"type" : "double"
}
}
}
},
"settings" : {
"index" : {
"number_of_shards" : "5",
"provided_name" : "280cap",
"max_result_window" : "5000000",
"creation_date" : "1536100881435",
"number_of_replicas" : "1",
"uuid" : "dzoTEMPxSdiC_BNswjlW2w",
"version" : {
"created" : "6030299"
}
}
}
}
}

Security (Parent) Document looks like this (Simplified):
{
"cusip": "914072TD5",
"myJoinField": "parent"
}

Multiple Offer (Child) Documents (Simplified):
[
{
"price": 101.532,
"cusip": "914072TD5",
"minQuantity": 5,
"maxQuantity": 5,
"isDeleted": false,
"id": "TITUSOFR-O_04-sep-2018_31_75389-20180904",
"myJoinField": {
"name": "offer",
"parent": "914072TD5"
}
},
{
"price": 103.957,
"cusip": "914072TD5",
"minQuantity": 20,
"maxQuantity": 20,
"isDeleted": false,
"id": "TITUSOFR-O_04-sep-2018_31_237004-20180904",
"myJoinField": {
"name": "offer",
"parent": "914072TD5"
}
},
{
"price": 117.005,
"cusip": "914072TD5",
"minQuantity": 10,
"maxQuantity": 10,
"isDeleted": false,
"id": "TITUSOFR-O_06-aug-2018_0_55656-20180904",
"myJoinField": {
"name": "offer",
"parent": "914072TD5"
}
}
]
Query looks like this:
{
"from": 0,
"size": 12,
"query": {
"bool": {
"filter": [
{
"exists": {
"field": "couponType"
}
}],
"must": [
{
"has_child" : {
"type" : "offer",
"score_mode" : "min",
"query" : {
"function_score": {
"query": {

                "bool": {
    "filter": [
      {
                
                
                "term" : {
                "isDeleted" : false
            }
                
      }],
      "must": [
        {
          "exists": {
            "field": "maxQuantity"
          }
        }
      ]          
                }
                
              },
        "field_value_factor": {
            "field": "maxQuantity",
            "factor": 1,
            "missing": 1
        },
              "boost_mode": "replace"
    }
        },
          "inner_hits": {
              "sort": [
      {
        "price": {
          "order": "asc"
        }
      }
	  ],
    "size": 1
          }
    }
  }]
}
},
"sort": [
    {
        "_score": {
            "order": "asc"
        }
    }
]

}

Basically, this is the approach:

1. Get the lowest price Offer (child) document for each Security (Parent). For this, in the search query, I use "has_child" and "inner_hits" with sort (on inner_hits) by price having size:1. This returns one Offer document that has the lowest price.
2. Then, I want to sort Security (Parent) documents based on the returned Offer (returned by the above inner_hits) document's maxQuantity value.

The above query does not sort the Security (Parent) documents by the maxQuantity (of the resulted Offer child document). This is because the function_score is applied on all the three Offer documents and picks the minimum score from all three (coz of score_mode being minimum). Ideally, the function_score should be applied only on the single Offer returned by the inner_hits.

What's the recommendation on achieving this?

Thanks a lot.
Arun

[arunjp]

The requirement is as such in a nutshell:

1. Have two documents Security and Offer in parent-child relationship respectively (using ElasticSearch 6.3.2 Join data type). Offer (child) doc contains price.
2. First get the best offer for a security, which is lowest price
3. Sort Security (parent) docs by a field in Offer (child) doc

Any expert advice is much appreciated. Thanks.
-Arun