Get aggregation value and source field in one st


(Teddy) #1

My Index has data as fields as

  1. dept_id
  2. dept_name
  3. dept_manager
  4. year
  5. month
  6. expenditure

there are multiple documents of each dept_id making expenditure corresponding to year and month.
I need to to get the total expenditure made by all dept in so and so year and month.

I can achieve this by :

GET Index/_search?size=0
{

  "query": {
    "bool": {
      "must": [
        {"match": { "year": 2017 } },
        {"match": { "month": 2  }         
      ]
    }
  },
  "aggs": {
    "By Dept": {
      "terms": {
        "field": "dept_id",
        "size": 50
      },
      "aggs": {
        "total Expenditure": {
          "sum": {
            "field": "expenditure"
          }
        }
      }
    }
 }

but this only gives me the total sum for each dept.

I require the following.

dept_id , dept_name, dept_manager, total expenditure made by the dept in month=3 and year=2017 in one single set


(Ayush Mathur) #2

I believe what you need is nested aggregation in this case:

"aggs": {
    "DeptID": {
      "terms": {
        "field": "dept_id",
        "size": 50
      },
      "aggs": {
         "DeptName" : {
            "terms" : {
                "field" : "dept_name.keyword",
                "size" : 50
            },
           "aggs" : {
               "DeptMngr" : {
                   "terms" : {
                         "field" : "dept_manager.keyword",
                         "size" : 50
                  },
                  "aggs": {
                       "total Expenditure": {
                            "sum": {
                                 "field": "expenditure"
                            }
                        }
                   }
              }
           }
        }
     }

(system) #3

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