Elasticsearch sort by children
<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>