Reputation: 65
I am trying to get total unique record and sum of amount of those unique data. How to get it in Elastic Search query.My Sample input data is
{"amt":"2.9",
"recName":"Item2",
"recID":"r1",
"exeDate":"2022-03-19T02:51:06.948Z",
"inputDate":"2022-03-19"}
{"amt":"2.9",
"recName":"Item2",
"recID":"r1",
"exeDate":"2022-03-19T02:52:06.948Z",
"inputDate":"2022-03-19"}
{"amt":"1.8",
"recName":"Item1",
"recID":"r2",
"exeDate":"2022-03-19T02:51:06.948Z",
"inputDate":"2022-03-19"}
I am expecting output:
recName: Item1, Item2
recNameCount : 2
amount: 4.7
Upvotes: 1
Views: 1207
Reputation: 5486
First, You can defind index mapping which have field amt
as float
type. like below:
{
"mappings": {
"properties": {
"amt":{
"type": "float"
},
"recName":{
"type": "keyword"
}
}
}
}
After, You can use aggregation for your expected result as below:
{
"size": 0,
"aggs": {
"RecName": {
"terms": {
"field": "recName",
"size": 10,
"order": {
"_key": "asc"
}
},
"aggs": {
"amount": {
"avg": {
"field": "amt"
}
}
}
},
"unique_count": {
"cardinality": {
"field": "recName"
}
},
"sum_total":{
"sum_bucket": {
"buckets_path": "RecName>amount"
}
}
}
}
recName
field which will give unique name.amt
field (inner aggregation insideRecName
to just get amout per item).recName
field for getting distinct values.amt
field.Response
"aggregations" : {
"RecName" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "Item1",
"doc_count" : 1,
"amount" : {
"value" : 1.7999999523162842
}
},
{
"key" : "Item2",
"doc_count" : 2,
"amount" : {
"value" : 2.9000000953674316
}
}
]
},
"unique_count" : {
"value" : 2
},
"sum_total" : {
"value" : 4.700000047683716
}
}
Upvotes: 1