title: Elasticsearch sort by children
body: <p>Two entities: collection and product. The collection is the parent of product.</p>
  <p>I need to search by product's terms and show collections with 4 products each.</p>
  <p>Collections and products can be partially matched, but the best match first. If the match is not full, some terms have priority.</p>
  <p>Example: Searching &quot;color:red&quot; and &quot;material:stone&quot; need to show red stones first, any other red next (this is about collections match and product match).</p>
  <p>So, all of this solved by request below:</p>
  <pre><code>{
    &quot;query&quot;: {
      &quot;has_child&quot;: {
        &quot;type&quot;: &quot;products&quot;,
        &quot;query&quot;: {
          &quot;bool&quot;: {
            &quot;should&quot;: [
              {
                &quot;constant_score&quot;: {
                  &quot;filter&quot;: {
                    &quot;match_all&quot;: {}
                  },
                  &quot;boost&quot;: 1
                }
              },
              {
                &quot;constant_score&quot;: {
                  &quot;filter&quot;: {
                    &quot;terms&quot;: { &quot;_name&quot;: &quot;colors&quot;, &quot;colors&quot;: [5] }
                  },
                  &quot;boost&quot;: 1.2
                }
              },
              {
                &quot;constant_score&quot;: {
                  &quot;filter&quot;: {
                    &quot;terms&quot;: { &quot;_name&quot;: &quot;materials&quot;, &quot;productTypes&quot;: [6] }
                  },
                  &quot;boost&quot;: 1
                }
              }
            ]
          }
        },
        &quot;score_mode&quot;: &quot;max&quot;,
        &quot;inner_hits&quot;: {
          &quot;size&quot;: 4,
          &quot;sort&quot;: [
            &quot;_score&quot;
          ]
        }
      }
    },
    &quot;sort&quot;: [
      &quot;_score&quot;
    ]
  }
  </code></pre>
  <p>Ok, now the trouble.</p>
  <p>Need to sort by price. As ASC, as DESC. Price is the property of the product.</p>
  <p>Need to sort by the price of matched products, so can't move price to the collection. Need to sort by price as a collection as products. Collections sorted by minimal (or maximal) price of matched products.</p>
  <p>Need to sort by price only 100% matched products (well, partially matched can be sorted too, but after). I mean, sort must be like ORDER BY _score, price</p>
  <p>Example, that I want to get, sort by price asc, [nn] means partially matched product:</p>
  <pre><code>Collection1
  100 - 200 - 800 - [99]
  Collection2
  300 - 500 - [10] - [20]
  Collection3
  400 - 450 - 500 - [100]
  </code></pre>
  <p>I found that sort by the child <a href="https://github.com/elastic/elasticsearch/issues/2917" rel="nofollow noreferrer">is not supported</a>. And the suggestion to recalculate score. But I'm using the score for sort by match.
  My try was</p>
  <pre><code>{
    &quot;query&quot;: {
      &quot;has_child&quot;: {
        &quot;type&quot;: &quot;products&quot;,
        &quot;query&quot;: {
          &quot;function_score&quot;: {
            &quot;query&quot;: {
              &quot;bool&quot;: {
                &quot;should&quot;: [
                  ... same query as above ...
                ]
              }
            },
            &quot;functions&quot;: [
              {
                &quot;script_score&quot;: {
                  &quot;script&quot;: &quot;ceil(_score * 100) * 100000 + (99999 - doc['price'].value/100)&quot;,
                  &quot;lang&quot;: &quot;expression&quot;
                }
              }
            ]
          }
        },
        &quot;score_mode&quot;: &quot;max&quot;,
        &quot;inner_hits&quot;: {
          &quot;size&quot;: 4,
          &quot;sort&quot;: [
            &quot;_score&quot;,
            {
              &quot;price&quot;: {
                &quot;order&quot;: &quot;desc&quot;
              }
            }
          ]
        }
      }
    },
    &quot;sort&quot;: [
      &quot;_score&quot;
    ]
  }
  </code></pre>
  <p>But I'm really confused with results to score that I can see in answer. Asking for help :) Or, maybe, drop this and create a nested index?</p>
  <p>UPD: Found that was wrong with score. By default, elastic combine score and result of script_score. So score was <code>ceil(_score * 100) * 100000 + (99999 - doc['price'].value/100) * _score</code> - that can broke idea, but easy to fix with <code>boost_mode</code> parameter of <code>function_score</code>. Result query:</p>
  <pre><code>{
    &quot;query&quot;: {
      &quot;has_child&quot;: {
        &quot;type&quot;: &quot;products&quot;,
        &quot;query&quot;: {
          &quot;function_score&quot;: {
            &quot;query&quot;: {
              &quot;bool&quot;: {
                &quot;should&quot;: [
                  ... same query as above ...
                ]
              }
            },
            &quot;functions&quot;: [
              {
                &quot;script_score&quot;: {
                  &quot;script&quot;: &quot;ceil((log10(_score)+10) * 100) * 100000 + (99999 - doc['price'].value)&quot;,
                  &quot;lang&quot;: &quot;expression&quot;
                }
              }
            ],
            &quot;boost_mode&quot;: &quot;replace&quot;
          }
        },
        &quot;score_mode&quot;: &quot;max&quot;,
        &quot;inner_hits&quot;: {
          &quot;size&quot;: 4,
          &quot;sort&quot;: [
            &quot;_score&quot;,
            {
              &quot;price&quot;: {
                &quot;order&quot;: &quot;desc&quot;
              }
            }
          ]
        }
      }
    },
    &quot;sort&quot;: [
      &quot;_score&quot;
    ]
  }
  </code></pre>
  <p><code>boost_mode == 'replace</code> means &quot;use function result as score&quot;. Also, used log10 to be sure how many digits in _score.
  For sort by price DESC need to change formula to <code>ceil((log10(_score)+10) * 100) * 100000 + (doc['price'].value)</code></p>
  <p><strong>UPD2</strong></p>
  <p>Formula <code>ceil((log10(_score)+10) * 100) * 100000 + (99999 - doc['price'].value)</code> returns <code>100099952</code> for price <code>48</code> and for price <code>50</code> (boost == 1, queryNorm == 1) because single precision limitation.</p>
  <p>New formula <code>ceil((log10(_score)+5) * 100) * 10000 + (9999 - ceil(log10(doc['price'].value) * 1000))</code> - reduced number of digits for score and switched from price to lg of price and reduced number of digits too. Feedback welcome.</p>