Get the most recent record which matches a field

Elastic Stack Elasticsearch
1

Hi,

When I perform this query:

{
	"query": {
		"match": {
			"service_id": "foo"
		}
	},
	"size": 1
}

I get one entry (the earliest by timestamp) that matches the desired service_id. However, I would like to retrieve the most recent matching entry. I do:

{
	"query": {
		"match": {
			"service_id": "foo"
		}
	},
	"sort": {
		"@timestamp": "desc"
	},
	"size": 1
}

But then I get the most recent entry regardless of service_id. Sometimes the service_id is "foo", but sometimes it's "bar", sometimes ... It's as if Elasticsearch completely ignores the "match" when I add the "sort" part. It doesn't help if I put the "sort" before the query, between the query and size, or at the end after the size.

Am I doing anything wrong? Could someone please help?

2

Welcome!

Could you provide a full recreation script as described in About the Elasticsearch category. It will help to better understand what you are doing. Please, try to keep the example as simple as possible.

A full reproduction script will help readers to understand, reproduce and if needed fix your problem. It will also most likely help to get a faster answer.

3

Hi, I performed a full reproduction. I put two entries like this:

DELETE index
PUT index/_doc/1
{
   "service_id": "this-is-my-foo",
   "timestamp": "2020-03-01T10:26:12.301Z"
}
PUT index/_doc/2
{
   "service_id": "this-is-my-bar",
   "timestamp": "2020-03-01T11:11:11.111Z"
}

Then I search like this:

POST /index/_search
{
  "query": {
    "match": {
      "service_id": "this-is-my-foo"
    }
  },
  "size": 1
}

This returns the entry with service_id "this-is-my-foo". However, if I add a sort to my query:

POST /index/_search
{
  "query": {
    "match": {
      "service_id": "this-is-my-foo"
    }
  },
  "sort": { "timestamp" : "desc" },
  "size": 1
}

it returns the entry with service_id "this-is-my-bar" (which does have a later timestamp, but I'm asking for entries that match service_id "this-is-my-foo").

The unexpected behavior does not happen if the "service_id"s are just "foo" and "bar", but it does happen if they have a common string at the start (which is also the case in my real data).

4

Try this:

GET /index/_search
{
  "query": {
    "match": {
      "service_id.keyword": "this-is-my-foo"
    }
  },
  "sort": { "timestamp" : "desc" },
  "size": 1
}
5

Thanks a lot, it worked. In the meantime I found another solution myself:

GET /index/_search
{
  "query": {
    "match": {
      "service_id": {
        "query": "this-is-my-foo",
        "minimum_should_match": "100%"
      }
    }
  },
  "sort": { "timestamp": "desc" },
  "size": 1
}
6

I'd not do that. Instead I'd use the right mapping for your use case.

7

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.