ElasticSearch - search_after pagination sort

[VJ052023]

I am trying to fetch results from Elasticsearch API using search_after for pagination.

However, in order to iterate to the subsequent data I am using a sort in the request Body of the API call as shown below

"sort": [ 
    {"@timestamp": {"order": "asc", "format": "strict_date_optional_time_nanos"}}
  ]

Which returns results as shown below:

[
[
"2019-09-09T09:15:01.000Z",0
]
[
"2019-09-09T09:15:01.000Z",1
]
[
"2019-09-09T09:15:01.000Z",2
]
[
"2019-09-09T09:24:55.000Z",289
]
[
"2019-09-09T09:24:55.000Z",308
]
[
"2019-09-09T09:24:55.000Z",315
]
[
"2019-09-09T09:55:00.000Z",3
]
[
"2019-09-09T09:55:00.000Z",4
]
[
"2019-09-09T09:55:00.000Z",5
]
[
"2019-09-10T19:07:07.000Z",288
]
]

So as per my understanding, To iterate to the next search I need to set additionally in the request body value of "search_after": ["2019-09-10T19:07:07.000Z",288 ] as it is the last hit value and I have set the size as 25 in the request body and total count of records is around 58, so ideally I should get all records in 3 iterations.

Still, I am not getting results of the second & third iterations, it only iterates the first time and prints the results of the first page, what am I missing here to see all data?

[RabBit_BR (andre.coelho)]

Hi @VJ052023

you order by the timestamp field and which other? Please provide your complete query for better assessment of the issue.

[VJ052023]

@VJ052023

["2019-09-10T19:07:07.000Z",288 ]

Hello @RabBit_BR

Below is the API endpoint with its request Body which I am using.
The only difference between the first and next calls is for the latter I am additonally adding the "search_after" parameter which has the last hit sort value from 1st iteration i.e. 2019-09-10T19:07:07.000Z, 288

GET https://<instance>/_search


Request Body

{
	"query": {
		"bool": {
			"must": [
				{
					"term": {
						"event.keyword": "userLogin"
					}
				}
			]
		}
	},
	"pit": {
		"id": "12345P356yufiwr",
		"keep_alive": "2m"
	},
	"sort": [
		{
			"@timestamp": {
				"order": "asc",
				"format": "strict_date_optional_time_nanos"
			}
		}
	],
	"search_after": [
		"2019-09-10T19:07:07.000Z",
		288
	],
	"aggs": {
		"by_customer": {
			"terms": {
				"field": "sf-account-id.keyword",
				"size": 25
			},
			"aggs": {
				"distinct_user_id_count": {
					"cardinality": {
						"field": "nlweb-userid.keyword"
					}
				}
			}
		}
	}
}

Additionally, below is the response I get if I don't use pagination and set the size parameter as 1000 instead of 25 as shown above for pagination

{
	"entity": {
		"took": 12,
		"timed_out": false,
		"_shards": {
			"total": 25,
			"successful": 25,
			"skipped": 0,
			"failed": 0
		},
		"hits": {
			"total": {
				"value": 10000,
				"relation": "gte"
			},
			"max_score": null,
			"hits": []
		},
		"aggregations": {
			"by_customer": {
				"doc_count_error_upper_bound": 0,
				"sum_other_doc_count": 0,
				"buckets": [
					{
						"key": "62eb9c2c38af76e5a3",
						"doc_count": 54323,
						"distinct_user_id_count": {
							"value": 3
						}
					},
					{
						"key": "60af7c295c17f8e32e30",
						"doc_count": 17984,
						"distinct_user_id_count": {
							"value": 5
						}
					},
					{
						"key": "00100001iiVS7",
						"doc_count": 13023,
						"distinct_user_id_count": {
							"value": 23
						}
					},
					{
						"key": "0012007uQAAS",
						"doc_count": 7463,
						"distinct_user_id_count": {
							"value": 49
						}
					},
					{
						"key": "592fe0013d8c0d",
						"doc_count": 1546,
						"distinct_user_id_count": {
							"value": 36
						}
					},
					{
						"key": "001glAAP",
						"doc_count": 753,
						"distinct_user_id_count": {
							"value": 26
						}
					},
					{
						"key": "5a26be48001f145ae",
						"doc_count": 511,
						"distinct_user_id_count": {
							"value": 7
						}
					},
					{
						"key": "62b9697afbf14063",
						"doc_count": 379,
						"distinct_user_id_count": {
							"value": 3
						}
					},
					{
						"key": "627cb421fc38e03",
						"doc_count": 317,
						"distinct_user_id_count": {
							"value": 11
						}
					},
					{
						"key": "62665451998ee47",
						"doc_count": 224,
						"distinct_user_id_count": {
							"value": 6
						}
					},
					{
						"key": "61f1b74d1b891aa7",
						"doc_count": 205,
						"distinct_user_id_count": {
							"value": 4
						}
					},
					{
						"key": "001g000XAAX",
						"doc_count": 142,
						"distinct_user_id_count": {
							"value": 15
						}
					},
					{
						"key": "62bafc475d5672112",
						"doc_count": 93,
						"distinct_user_id_count": {
							"value": 4
						}
					},
					{
						"key": "61f93d7665b0c95",
						"doc_count": 54,
						"distinct_user_id_count": {
							"value": 1
						}
					},
					{
						"key": "641d6447bb20e51c",
						"doc_count": 44,
						"distinct_user_id_count": {
							"value": 3
						}
					},
					{
						"key": "001g00GRiSAAX",
						"doc_count": 37,
						"distinct_user_id_count": {
							"value": 3
						}
					},
					{
						"key": "001g0r7C",
						"doc_count": 36,
						"distinct_user_id_count": {
							"value": 2
						}
					},
					{
						"key": "5a28043be001f145c5",
						"doc_count": 27,
						"distinct_user_id_count": {
							"value": 3
						}
					},
					{
						"key": "644fedf0cc0aa0ff3",
						"doc_count": 21,
						"distinct_user_id_count": {
							"value": 1
						}
					},
					{
						"key": "6329b29fbebbe1f8",
						"doc_count": 18,
						"distinct_user_id_count": {
							"value": 2
						}
					},
					{
						"key": "61fcf2f2e31256945",
						"doc_count": 15,
						"distinct_user_id_count": {
							"value": 1
						}
					},
					{
						"key": "6213b310753acb87b",
						"doc_count": 13,
						"distinct_user_id_count": {
							"value": 1
						}
					},
					{
						"key": "624df3160241cb5bb44",
						"doc_count": 13,
						"distinct_user_id_count": {
							"value": 1
						}
					},
					{
						"key": "001g0000GAA5",
						"doc_count": 9,
						"distinct_user_id_count": {
							"value": 1
						}
					},
					{
						"key": "62d56e30caf768d65",
						"doc_count": 9,
						"distinct_user_id_count": {
							"value": 1
						}
					},
					{
						"key": "001g0000khEAAT",
						"doc_count": 8,
						"distinct_user_id_count": {
							"value": 2
						}
					},
					{
						"key": "001g0iIAAX",
						"doc_count": 8,
						"distinct_user_id_count": {
							"value": 3
						}
					},
					{
						"key": "63278240a6fdb00852e",
						"doc_count": 8,
						"distinct_user_id_count": {
							"value": 2
						}
					},
					{
						"key": "0000JT06CAAT",
						"doc_count": 7,
						"distinct_user_id_count": {
							"value": 2
						}
					},
					{
						"key": "62f558770994271",
						"doc_count": 7,
						"distinct_user_id_count": {
							"value": 1
						}
					},
					{
						"key": "toto",
						"doc_count": 7,
						"distinct_user_id_count": {
							"value": 2
						}
					},
					{
						"key": "5f0c7254aabef2b237",
						"doc_count": 6,
						"distinct_user_id_count": {
							"value": 1
						}
					},
					{
						"key": "61f25ab679b891d7d",
						"doc_count": 6,
						"distinct_user_id_count": {
							"value": 1
						}
					},
					{
						"key": "633eaab8c84c12390cf",
						"doc_count": 6,
						"distinct_user_id_count": {
							"value": 1
						}
					},
					{
						"key": "61f94e9c7f400",
						"doc_count": 5,
						"distinct_user_id_count": {
							"value": 2
						}
					},
					{
						"key": "622b633f2b062f18824",
						"doc_count": 5,
						"distinct_user_id_count": {
							"value": 1
						}
					},
					{
						"key": "5c7646c0a10d77f3",
						"doc_count": 4,
						"distinct_user_id_count": {
							"value": 3
						}
					},
					{
						"key": "5f0c6b3faa1f2b234",
						"doc_count": 4,
						"distinct_user_id_count": {
							"value": 1
						}
					},
					{
						"key": "61fbf39921c16d551",
						"doc_count": 4,
						"distinct_user_id_count": {
							"value": 1
						}
					},
					{
						"key": "633eaaa6c390ce",
						"doc_count": 4,
						"distinct_user_id_count": {
							"value": 1
						}
					},
					{
						"key": "642c0ee3a9cc1c48f4",
						"doc_count": 4,
						"distinct_user_id_count": {
							"value": 1
						}
					},
					{
						"key": "00120000fvAAB",
						"doc_count": 3,
						"distinct_user_id_count": {
							"value": 1
						}
					},
					{
						"key": "6269031b8499424b",
						"doc_count": 3,
						"distinct_user_id_count": {
							"value": 1
						}
					},
					{
						"key": "62690b7b7f084994263",
						"doc_count": 3,
						"distinct_user_id_count": {
							"value": 1
						}
					},
					{
						"key": "620a711263a223e5",
						"doc_count": 2,
						"distinct_user_id_count": {
							"value": 1
						}
					},
					{
						"key": "62690b627084994261",
						"doc_count": 2,
						"distinct_user_id_count": {
							"value": 1
						}
					},
					{
						"key": "626a8b657f08499426d",
						"doc_count": 2,
						"distinct_user_id_count": {
							"value": 1
						}
					},
					{
						"key": "627bdf64acf0e1ce8",
						"doc_count": 2,
						"distinct_user_id_count": {
							"value": 1
						}
					},
					{
						"key": "6436d66b248f373b90f",
						"doc_count": 2,
						"distinct_user_id_count": {
							"value": 1
						}
					},
					{
						"key": "644f40ef8c0aa0feb",
						"doc_count": 2,
						"distinct_user_id_count": {
							"value": 1
						}
					},
					{
						"key": "61fa3dc51713f7a5",
						"doc_count": 1,
						"distinct_user_id_count": {
							"value": 1
						}
					},
					{
						"key": "63fddb03b4d3ad5",
						"doc_count": 1,
						"distinct_user_id_count": {
							"value": 1
						}
					},
					{
						"key": "63fde0bf0a9dc",
						"doc_count": 1,
						"distinct_user_id_count": {
							"value": 1
						}
					},
					{
						"key": "63fe146f103ddab22e",
						"doc_count": 1,
						"distinct_user_id_count": {
							"value": 1
						}
					},
					{
						"key": "63f103ddab22f",
						"doc_count": 1,
						"distinct_user_id_count": {
							"value": 1
						}
					},
					{
						"key": "6400aae99f7",
						"doc_count": 1,
						"distinct_user_id_count": {
							"value": 1
						}
					},
					{
						"key": "642becc1c486a",
						"doc_count": 1,
						"distinct_user_id_count": {
							"value": 1
						}
					},
					{
						"key": "6447cc0a9feb1",
						"doc_count": 1,
						"distinct_user_id_count": {
							"value": 1
						}
					}
				]
			}
		}
	}
}

is pagination not working because, in api request body, I am using Bucket aggregations?

[RabBit_BR (andre.coelho)]

@VJ052023

10T19:07:07.000Z, 288

In your query you have one field in sort but in search after it has two parameters. Do you use any tiebreaker id?

[VJ052023]

Hello @RabBit_BR Yes, In my request query for sort I am just passing 1 parameter of @timestamp

However, Since I am also using PIT (pit.id) so internally, the tiebreaker i.e. the second value (parameter) that I am passing to search_after is auto-generated.

This is also stated in the elastic API guide

If you used a PIT, a tiebreaker is included as the last sort values for each hit. This tiebreaker called _shard_doc is added automatically on every search requests that use a PIT. The _shard_doc value is the combination of the shard index within the PIT and the Lucene’s internal doc ID, it is unique per document and constant within a PIT.