Latest result for a unique combination of fields using Elasticsearch

user_1163551 0
Viewed 88

I have documents of the following format:

{name: 'A', website: 'example.com', date: 1, + other fields}
{name: 'A', website: 'example.com', date: 2, + other fields}
{name: 'B', website: 'example.com', date: 2, + other fields}
{name: 'A', website: 'something.com', date: 1, + other fields}
{name: 'A', website: 'something.com', date: 2, + other fields}
{name: 'C', website: 'something.com', date: 1, + other fields}
{name: 'C', website: 'something.com', date: 2, + other fields}

I would like to multi-query on name and website, while also returning only the latest result. My query looks like this:

query: {
    bool: {
      ...optional filters...,
      must: {
        multi_match: {
          query: input,
          type: "most_fields",
          fields: ["name^3", ..., "website"],
        },
      },
    },
  },

My desired output should look like this, ordered by _score:

{name: 'A', website: 'example.com', date: 2, + other fields}
{name: 'B', website: 'example.com', date: 2, + other fields}
{name: 'A', website: 'something.com', date: 2, + other fields}
{name: 'C', website: 'something.com', date: 2, + other fields}

Now I understand that an agg is required to get the latest result using top_hits, e.g.:

top_hits: {
  size: 1,
  sort: [{ date: "desc" }],
},

However, in the process of aggregating by website then by name, I lose the ordering by _score which is important for my query. I have already tried using composite agg, how it's not possible to order it by the score of the resulting records.

elasticsearch-aggregation elasticsearch
2 Answers

I am considering using an extra manually create a field which is a concatenation of name and website which I can then use as a single level aggregation which will then allow me to sort the keys by _score. E.g.:

  aggs: {
    latest_results: {
      terms: {
        field: "website_name.keyword",
        order: {
          maximum_score: "desc",
        },
      },
      aggs: {
        maximum_score: {
          max: {
            script: {
              source: "_score",
            },
          },
        },
        hits: {
          top_hits: {
            size: 1,
            sort: [{ date: "desc" }],
          },
        },
      },
    },
  },
user_11635510

You should be able to do this with a top hits agg in a term agg using a script. According to documentation of top_hits

sort - How the top matching hits should be sorted. By default the hits are sorted by the score of the main query.

{
  "size": 0, 
  "query": {
    "bool": {
      "must": [
        {"multi_match": {
          "query": "A",
          "type": "most_fields",
          "fields": ["name^3", "website"]
        }}
      ]
    }
  },
  "aggs": {
    "visitor": {
      "terms": {
       "script": "doc['name'].value +'-'+ doc['website'].value",
        "size": 10
      },
      "aggs": {
        "top_visitors": {
          "top_hits": {
            "size": 1
            
          }
        }
      }
    }
  }
}

Your result would look like this :

"visitor" : {
  "doc_count_error_upper_bound" : 0,
  "sum_other_doc_count" : 0,
  "buckets" : [
    {
      "key" : "A-example.com",
      "doc_count" : 2,
      "top_visitors" : {
        "hits" : {
          "total" : {
            "value" : 2,
            "relation" : "eq"
          },
          "max_score" : 1.7260926,
          "hits" : [
            {
              "_index" : "test-52",
              "_type" : "_doc",
              "_id" : "vu_xUnQB5HlCKIdlWRy8",
              "_score" : 1.7260926,
              "_source" : {
                "name" : "A",
                "website" : "example.com",
                "date" : 1
              }
            }
          ]
        }
      }
    },
    {
      "key" : "A-something.com",
      "doc_count" : 2,
      "top_visitors" : {
        "hits" : {
          "total" : {
            "value" : 2,
            "relation" : "eq"
          },
          "max_score" : 1.7260926,
          "hits" : [
            {
              "_index" : "test-52",
              "_type" : "_doc",
              "_id" : "VWDxUnQBx_BqvGcp8U8j",
              "_score" : 1.7260926,
              "_source" : {
                "name" : "A",
                "website" : "something.com",
                "date" : 1
              }
            }
          ]
        }
      }
    }
  ]
}

Just be careful with performance aggregation with script can use a lot of resources and be quite slow.

user_25639460
Related