# Sort Parent Documents based on Inner\_Hits result object field

**URL:** <https://discuss.elastic.co/t/sort-parent-documents-based-on-inner-hits-result-object-field/147625>\
**Category:** Elasticsearch\
**Created:** [September 6, 2018, 7:25pm UTC](https://discuss.elastic.co/t/sort-parent-documents-based-on-inner-hits-result-object-field/147625 "2018-09-06T19:25:15Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![arunjp](https://avatars.discourse-cdn.com/v4/letter/a/b9bd4f/32.png) [@arunjp](https://discuss.elastic.co/u/arunjp)\
**Post date:** [September 6, 2018, 7:25pm UTC](https://discuss.elastic.co/t/sort-parent-documents-based-on-inner-hits-result-object-field/147625/1 "2018-09-06T19:25:15Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![arunjp](https://avatars.discourse-cdn.com/v4/letter/a/b9bd4f/32.png) [@arunjp](https://discuss.elastic.co/u/arunjp)\
**Post date:** [September 7, 2018, 4:49pm UTC](https://discuss.elastic.co/t/sort-parent-documents-based-on-inner-hits-result-object-field/147625/2 "2018-09-07T16:49:37Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [October 5, 2018, 4:49pm UTC](https://discuss.elastic.co/t/sort-parent-documents-based-on-inner-hits-result-object-field/147625/3 "2018-10-05T16:49:55Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.