Elasticsearch - using nested object value in Function Score

Question

I currently have a nested object interest_scores in ES that looks like this:

[{
    username: 'Somebody',
    interest_scores: [
        { name: 'Running', score: 10 }
        { name: 'Food and drinks', score: 21 }
    ]
},
{
    username: 'SomebodyElse',
    interest_scores: [
        { name: 'Running', score: 7 }
        { name: 'Food and drinks', score: 29 }
    ]
}]

When I enter the search term Running I would like the user with the highest score for Running to get returned first.

I know the way to do this is to use a Function Score Query but I am not sure how to use the matching search term in the function / script. What I think is that the query will return all documents that have the interest "Running" and then I could use something like interest_scores.{match}.score to add to or multiply by the document score.

Any help with this would be greatly appreciated!

As requested, here is the mapping:

{
  "influencers": {
    "mappings": {
      "influencer": {
        "properties": {
          "email": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "gender": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "geo": {
            "type": "geo_point"
          },
          "hashtags": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "id": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "interest_scores": {
            "type": "nested",
            "properties": {
              "name": {
                "type": "text",
                "fields": {
                  "keyword": {
                    "type": "keyword",
                    "ignore_above": 256
                  }
                }
              },
              "score": {
                "type": "long"
              }
            }
          },
          "interests": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "language": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "location": {
            "properties": {
              "city": {
                "type": "text",
                "fields": {
                  "keyword": {
                    "type": "keyword",
                    "ignore_above": 256
                  }
                }
              },
              "country": {
                "type": "text",
                "fields": {
                  "keyword": {
                    "type": "keyword",
                    "ignore_above": 256
                  }
                }
              },
              "country_code": {
                "type": "text",
                "fields": {
                  "keyword": {
                    "type": "keyword",
                    "ignore_above": 256
                  }
                }
              },
              "lat": {
                "type": "float"
              },
              "lng": {
                "type": "float"
              },
              "state_code": {
                "type": "text",
                "fields": {
                  "keyword": {
                    "type": "keyword",
                    "ignore_above": 256
                  }
                }
              },
              "subdivision": {
                "type": "text",
                "fields": {
                  "keyword": {
                    "type": "keyword",
                    "ignore_above": 256
                  }
                }
              }
            }
          },
          "network_data": {
            "properties": {
              "facebook": {
                "properties": {
                  "url": {
                    "type": "text",
                    "fields": {
                      "keyword": {
                        "type": "keyword",
                        "ignore_above": 256
                      }
                    }
                  },
                  "username": {
                    "type": "text",
                    "fields": {
                      "keyword": {
                        "type": "keyword",
                        "ignore_above": 256
                      }
                    }
                  }
                }
              },
              "instagram": {
                "properties": {
                  "bio": {
                    "type": "text",
                    "fields": {
                      "keyword": {
                        "type": "keyword",
                        "ignore_above": 256
                      }
                    }
                  },
                  "engagement": {
                    "type": "float"
                  },
                  "id": {
                    "type": "text",
                    "fields": {
                      "keyword": {
                        "type": "keyword",
                        "ignore_above": 256
                      }
                    }
                  },
                  "picture": {
                    "type": "text",
                    "fields": {
                      "keyword": {
                        "type": "keyword",
                        "ignore_above": 256
                      }
                    }
                  },
                  "reach": {
                    "type": "long"
                  },
                  "url": {
                    "type": "text",
                    "fields": {
                      "keyword": {
                        "type": "keyword",
                        "ignore_above": 256
                      }
                    }
                  },
                  "username": {
                    "type": "text",
                    "fields": {
                      "keyword": {
                        "type": "keyword",
                        "ignore_above": 256
                      }
                    }
                  }
                }
              },
              "pinterest": {
                "properties": {
                  "url": {
                    "type": "text",
                    "fields": {
                      "keyword": {
                        "type": "keyword",
                        "ignore_above": 256
                      }
                    }
                  },
                  "username": {
                    "type": "text",
                    "fields": {
                      "keyword": {
                        "type": "keyword",
                        "ignore_above": 256
                      }
                    }
                  }
                }
              },
              "twitter": {
                "properties": {
                  "bio": {
                    "type": "text",
                    "fields": {
                      "keyword": {
                        "type": "keyword",
                        "ignore_above": 256
                      }
                    }
                  },
                  "engagement": {
                    "type": "float"
                  },
                  "id": {
                    "type": "text",
                    "fields": {
                      "keyword": {
                        "type": "keyword",
                        "ignore_above": 256
                      }
                    }
                  },
                  "picture": {
                    "type": "text",
                    "fields": {
                      "keyword": {
                        "type": "keyword",
                        "ignore_above": 256
                      }
                    }
                  },
                  "reach": {
                    "type": "long"
                  },
                  "url": {
                    "type": "text",
                    "fields": {
                      "keyword": {
                        "type": "keyword",
                        "ignore_above": 256
                      }
                    }
                  },
                  "username": {
                    "type": "text",
                    "fields": {
                      "keyword": {
                        "type": "keyword",
                        "ignore_above": 256
                      }
                    }
                  }
                }
              },
              "youtube": {
                "properties": {
                  "bio": {
                    "type": "text",
                    "fields": {
                      "keyword": {
                        "type": "keyword",
                        "ignore_above": 256
                      }
                    }
                  },
                  "engagement": {
                    "type": "float"
                  },
                  "id": {
                    "type": "text",
                    "fields": {
                      "keyword": {
                        "type": "keyword",
                        "ignore_above": 256
                      }
                    }
                  },
                  "picture": {
                    "type": "text",
                    "fields": {
                      "keyword": {
                        "type": "keyword",
                        "ignore_above": 256
                      }
                    }
                  },
                  "reach": {
                    "type": "long"
                  },
                  "url": {
                    "type": "text",
                    "fields": {
                      "keyword": {
                        "type": "keyword",
                        "ignore_above": 256
                      }
                    }
                  },
                  "username": {
                    "type": "text",
                    "fields": {
                      "keyword": {
                        "type": "keyword",
                        "ignore_above": 256
                      }
                    }
                  },
                  "videos": {
                    "type": "long"
                  },
                  "views": {
                    "type": "long"
                  },
                  "views_per_video": {
                    "type": "float"
                  }
                }
              }
            }
          },
          "networks": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "picture": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "total_reach": {
            "type": "long"
          },
          "username": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          }
        }
      }
    }
  }
}

I do not have a function score query yet, I am only testing in the Dev Tools of Kibana - I do have all of the other filters working correctly though. I am just looking to say "If the search term matches a interest_scores.name then sort the hits by the interest_scores.score of that interest_scores.name

Update

The following seems to be working when I test it in Kibana dev tools:

{
  "query": {
    "nested": {
      "path": "interest_scores",
      "score_mode": "sum",
      "query": {
        "function_score": {
          "query": {
              "match": { "interest_scores.name": "Running" }
          },
          "script_score": {
            "script": "_score + doc['interest_scores.score'].value"
          }
        }
      }
    }
  }
}

I have tested it with a few different search terms and it always returns the highest score first, but what is weird is that I get the same results when I remove the script_score function. Can anyone tell me if this is a good solution, or why it works without the script_score?

Eli Eli · Accepted Answer · 2017-09-11T08:53:25

As described here, you can sort by nested fields:

{
  "_source": false,  # for inner hits - you can remove it
  "query": {
    "nested": {
      "path": "interest_scores",
      "filter": {
        "range": {
          "interest_scores.score": {
            "gte": "0"
          }
        }
      },
      "inner_hits": {}  # for inner hits - you can remove it
    }
  },
  "sort": {
    "interest_scores.score": {
      "order": "desc",
      "mode": "max",
      "nested_filter": {
        "range": {
          "interest_scores.score": {
            "gte": "0"
          }
        }
      }
    }
  }
}

*Pay attention that, you can use the inner_hits ability to show only relevant nested documents. If all inner hits documents are relevant - please remove the marked lines.

**Use the filter on score field or on any other field (e.g: name you would like to filter by).

EDIT 1: If you want to get the sorted scores of specific name, try:

{
  "_source": false,
  "query": {
    "nested": {
      "path": "interest_scores",
      "filter": {
        "term": {
          "interest_scores.name": "SCORE_NAME"
        }
      },
      "inner_hits": {}
    }
  },
  "sort": {
    "interest_scores.score": {
      "order": "desc",
      "mode": "max",
      "nested_filter": {
        "range": {
          "interest_scores.score": {
            "gte": "0"
          }
        }
      }
    }
  }
}

Put the desired score name instead SCORE_NAME.

Elasticsearch - using nested object value in Function Score

1 Answers