Find all documnents with max value in field-CodePudding

I am new in elasticsearch, it is my first NoSql DB and I have some problem.

I have something like this:

index_logs: [
      {
          "entity_id": id_1, 
          "field_name": "name",
          "old_value": null
          "new_value": "some_name"
          "number": 1
      }, 
      {
          "entity_id": id_1, 
          "field_name": "description",
          "old_value": null
          "new_value": "some_descr"
          "number": 1
      }, 
      {
          "entity_id": id_1, 
          "field_name": "description",
          "old_value": "some_descr"
          "new_value": null
          "number": 2
      }, 
      {
          "entity_id": id_2, 
          "field_name": "enabled",
          "old_value": true
          "new_value":false
          "number": 25
      }, 
]

And I need to find all documents with specified entity_id and max_number which I do not know.

In postgres it will be as following code:

SELECT * 
FROM logs AS x
WHERE x.entity_id = <some_entity_id>
AND x.number = (SELECT MAX(y.number) FROM logs AS y WHERE y.entity_id = x.entity_id)

How can I do it in elasticsearch?

CodePudding user response：

Use the following query:

{
  "query": {
    "term": {
      "entity_id": {
        "value": "1"
      }
    }
  },
  "aggs": {
    "max_number": {
      "terms": {
        "field": "number",
        "size": 1,
        "order": {
          "_key": "desc"
        }
      },
      "aggs": {
        "top_hits": {
          "top_hits": {
            "size": 10
          }
        }
      }
    }
  }
}

The aggregation will group by the "number", sort by descending order of number, then give you the top 10 results with that number, inside the 'top_hits' subaggregation.

Another way to get "All" the documents is to simply use the same query, withouty any aggregations, and sort descending on the "number" field. On the client side you use pagination with "search_after", and paginate all the results, till the "number" field changes. The first time the number changes, you exit your pagination loop and you have all the records with the given entity id and the max number.