1

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

1 Answer 1

1

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"
      }
    }
  }
}

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
    }
  }
Sign up to request clarification or add additional context in comments.

3 Comments

can present the same visually
Sorry i didnt get what you means @NadaNasser
I mean to build a visualization data table to present the output on Kibana dashboard. How to set the query elements when building a kibana visualization

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.