Sort Documents based on nested child count in ElasticSearch

Question

My Elasticserch index Index Name = movies

{
   "title": "Chris ENDGAME",
   "cast": [
      {
         "firstName": "Chris",
         "lastName": "Evans"
      },
      {
         "firstName": "Chris",
         "lastName": "Hemsworth"
      },
      {
         "firstName": "Chris",
         "lastName": "Prat"
      }
   ]
}

Similarly, I have 3 more movie documents

Movies2: Winter Soldier

cast: Chris Evans, Scarlett Johanson

Movies3: Ant-Man

cast: Paul Rudd, Michael Pena

Movies4: Avengers

cast: Chris Evans, Chris Hemsworth

With this, now I have 4 movies: 1. Endgame; 2.Winter Soldier; 3.Ant-Man; 4.Avengers

Now, I want to create an elasticsearch7 search query where if I search Chris' (overall: both title and first name) in an order of the number of matches per index searched.

i.e., OUTPUT(ordered) = Movies1,Movies4,Movies2, because movie1 has 4 , Movies4 has 2 and Movies2 has 1 chris matching in firstname

Till now, I have been able to write a basic query but I have no idea how to order the documents

My Search Query

{
  "query": {
    "bool": {
      "must": [
        { "multi_match": { "query": "Chris" }}
      ]
    }
  }
}

How do I order it?

Luc E · Accepted Answer · 2020-04-02T07:32:35.173

First of all, you should provide a nested mapping of your field "cast" as well :

PUT test_movies
{
  "mappings": {
    "properties": {
      "cast": {
        "type": "nested", 
        "properties": {
          "firstName": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "lastName": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          }
        }
      },
      "title": {
        "type": "text",
        "fields": {
          "keyword": {
            "type": "keyword",
            "ignore_above": 256
          }
        }
      }
    }
  }
}

Note that this is the default Elastic Mapping with both keyword and text for each field but the best practice is to specify each field if it's keyword, text or both. Also, in order to change your index mapping, you have to delete and recreate it

Now that your field "cast", is declared as nested, you can do a nested query on it :

POST test_movies/_search
{
  "query": {
    "nested": {
      "path": "cast",
      "query": {
        "match": {
          "cast.firstName": "Chris"
        }
      },
      "score_mode": "sum"
    }
  }
}

The "score_mode": "sum" will add the score for each sub-match on your nested field

EDIT

If you want to search both on the title and the nested child you have to compose with bool Query, so your query will be like this :

POST test_movies/_search
{
  "query": {
    "bool": {
      "should": [
        {
          "match": {
            "title": "Chris"
          }
        },
        {
          "nested": {
            "path": "cast",
            "query": {
              "match": {
                "cast.firstName": "Chris"
              }
            },
            "score_mode": "sum"
          }
        }
      ]
    }
  }
}

Thanks for the reply Luc E, but the question has changed and rather than searching only the first name I need to check the whole movie (i.e. including the title). I have updated the question. P.S. my mapping is the same as you described — Pankaj Yadav, Apr 02 '20 at 05:48

Sort Documents based on nested child count in ElasticSearch

1 Answers1