Elastic Search getting the latest of each document by ID and language

user_1265205 0
Viewed 130

My company has a CMS and the visits to its content is logged to Elastic Search, each entry is a document like this:

{
  "datetime": "2020-08-02T17:12:54.012+01:00",
  "title" : "title of the content",
  "LocalNumber" : "000025",
  "CodeLanguage" : "eng"
}

There are more but these are the important ones. The title property changes over time meaning that the ID (the LocalNumber) is the same but the title may be different.

I'm trying to make an ES query to retrieve just the lastest document by LocalNumber and CodeLanguage. I could achieve doing it by LocalNumber, using field collapse:

{
    "query" : {
        "match_all" : {}
    },
    "collapse": {
        "field": "LocalNumber.keyword"
    },
    "sort": [{"datetime": "desc"}]
}

How should I change this query to be able to get it by LocalNumber and CodeLanguage?

elasticsearch
2 Answers

Looks like you may just use second level of collapsing in collapse:

{
    "query": {
        "match_all": {}
    },
    "collapse": {
        "field": "LocalNumber.keyword",
        "inner_hits": {
            "name": "by lang",
            "collapse": {
                "field": "CodeLanguage.keyword"
            },
            "size": 3
        }
    },
    "sort": [
        {
            "datetime": "desc"
        }
    ]
}

This will return:

  • 1 document per LocalNumber.keyword, most recent by datetime first (your original query);
  • per each such document, up to 3 inner_hits documents per CodeLanguage.keyword.

With my test data it returns a result like the following:

{
  "hits": {
    "hits": [
      {
        "_index": "myindex2",
        "_type": "_doc",
        "_id": "2",
        "_score": null,
        "_source": {
          "datetime": "2020-08-02T17:12:54.012+01:00",
          "title": "title of the content 2",
          "LocalNumber": "000025",
          "CodeLanguage": "fr"
        },
        "fields": {
          "LocalNumber.keyword": [
            "000025"
          ]
        },
        "sort": [
          1596384774012
        ],
        "inner_hits": {
          "by lang": {
            "hits": {
              "total": {
                "value": 4,
                "relation": "eq"
              },
              "max_score": null,
              "hits": [
                {
                  "_index": "myindex2",
                  "_type": "_doc",
                  "_id": "2",
                  "_score": 1.0,
                  "_source": {
                    "datetime": "2020-08-02T17:12:54.012+01:00",
                    "title": "title of the content 2",
                    "LocalNumber": "000025",
                    "CodeLanguage": "fr"
                  },
                  "fields": {
                    "CodeLanguage.keyword": [
                      "fr"
                    ]
                  }
                },
                {
                  "_index": "myindex2",
                  "_type": "_doc",
                  "_id": "10",
                  "_score": 1.0,
                  "_source": {
                    "datetime": "2020-08-01T17:12:54.012+01:00",
                    "title": "title of the content 10",
                    "LocalNumber": "000025",
                    "CodeLanguage": "eng"
                  },
                  "fields": {
                    "CodeLanguage.keyword": [
                      "eng"
                    ]
                  }
                }
              ]
            }
          }
        }
      }
    ]
  }
}

I believe this is just a syntactic sugar for normal Elasticsearch aggregations, like the one joe posted above. So underneath ES will be doing the same thing, but this query is smaller and probably easier to understand.

user_50959570

You may as well aggregate by those two fields separately & then use top_hits to get only the latest doc:

{
  "size": 0,
  "aggs": {
    "by_LocalNumber": {
      "terms": {
        "field": "LocalNumber.keyword"
      },
      "aggs": {
        "latest": {
          "top_hits": {
            "size": 1,
            "sort": {
              "datetime": {
                "order": "desc"
              }
            }
          }
        }
      }
    },
    "by_CodeLanguage": {
      "terms": {
        "field": "CodeLanguage.keyword"
      },
      "aggs": {
        "latest": {
          "top_hits": {
            "size": 1,
            "sort": {
              "datetime": {
                "order": "desc"
              }
            }
          }
        }
      }
    }
  }
}

And if you're interested in a subset of LocalNumber & CodeLanguage, you can constrain them in a should query.

user_81603180
Related