Hi,
can someone help me with query:
get last version of document per composite of fields(author, type, name)
{"author" : "Mike", "type" : "expense_report", "name": "trip to europe 2023", "updated" : "1727544249"} <-returned
{"author" : "Mike", "type" : "expense_report", "name": "trip to europe 2025", "updated" : "1737542249"}
{"author" : "Mike", "type" : "expense_report", "name": "trip to europe 2025", "updated" : "1737543249"}
{"author" : "Mike", "type" : "expense_report", "name": "trip to europe 2025", "updated" : "1737544249"} <-returned
{"author" : "John", "type" : "expense_report", "name": "trip to europe 2023", "updated" : "1727544249"} <-returned
{"author" : "John", "type" : "expense_report", "name": "trip to europe 2025", "updated" : "1737544249"} <-returned
additionaly I will need to limit returned document and be able to search using "search_after"(after document given by author, type and name)
jessgarson
(Jess Garson)
January 22, 2025, 11:58pm
2
Thanks, @bzyku11 . Do you have an example of the query you were using to get the results you outlined? Also, what version of Elastic are you using?
ES 8, and the query I got for now is:
{
"query" : {
"match_all" : {}
},
"size": 0,
"aggs": {
"my_buckets": {
"composite": {
"size": 10,
"sources": [
{
"fullName": {
"terms": {
"field": "author.exact",
"order": "asc"
}
}
},
{
"debtorNumber": {
"terms": {
"field": "type.exact",
"order": "asc"
}
}
},
{
"clientDebtorReference": {
"terms": {
"field": "name.exact",
"order": "asc"
}
}
}
]
},
"aggs": {
"filtered_by_conditions": {
"filter": {
"bool": {
"must": [
{
"query_string": {
"analyze_wildcard": true,
"fields": [
"author.exact"
],
"lenient": true,
"query": "Mike"
}
}
]
}
},
"aggs": {
"latest_document": {
"top_hits": {
"size": 1,
"sort": [
{
"updated": {
"order": "desc"
}
}
]
}
}
}
},
"filter_buckets_with_hits": {
"bucket_selector": {
"buckets_path": {
"doc_count": "filtered_by_conditions._count"
},
"script": "params.doc_count > 0"
}
}
}
}
}
}
problem I got now is with limit(for pagination with "after" key) of result with used filtering. If first n buckets(in example 10) will not match filtered criteria(0 documents in bucket) I will not get any results
jessgarson
(Jess Garson)
January 23, 2025, 9:23pm
4
Thanks for following up, you may want to filter the documents at the query level so that the composite aggregation operates only on the relevant documents. This way, the pagination (after key) works seamlessly without empty buckets.
Something like this might be helpful:
{
"query": {
"bool": {
"must": [
{
"term": {
"author.exact": "Mike"
}
}
]
}
},
"size": 0,
"aggs": {
"my_buckets": {
"composite": {
"size": 10,
"sources": [
{
"fullName": {
"terms": {
"field": "author.exact",
"order": "asc"
}
}
},
{
"debtorNumber": {
"terms": {
"field": "type.exact",
"order": "asc"
}
}
},
{
"clientDebtorReference": {
"terms": {
"field": "name.exact",
"order": "asc"
}
}
}
],
"after": {
"fullName": "last_fullName_value",
"debtorNumber": "last_debtorNumber_value",
"clientDebtorReference": "last_clientDebtorReference_value"
}
},
"aggs": {
"latest_document": {
"top_hits": {
"size": 1,
"sort": [
{
"updated": {
"order": "desc"
}
}
]
}
}
}
}
}
}