ElasticSearch Nested Filter and Aggregation

Question

So I have this mapping:

"employee": {
  "properties": {
     "DaysOff": {
        "type": "nested",
        "properties": {
           "Date": {
              "type": "date",
              "format": "strict_date_optional_time||epoch_millis"
           },
           "Days": {
              "type": "double"
           },
           "ID": {
              "type": "long"
           }
        }
     }
  }
}

So basically a employee can have days off. Each day off they have is stored in an array under the property DaysOff. Days can be a fraction of a day, so if an employee took half a day off then it would be 0.5.

So I have this search:

{
   "size": 45,
   "filter": {
      "nested": {
         "path": "DaysOff",
         "filter": {
            "range": {
               "DaysOff.Date": {
                  "from": "now-2M",
                  "to": "now"
               }
            }
         }
      }  
   }
}

which brings me back 45 documents. which is correct. I'm just can't figure out how to now apply an aggregation to these documents in order to get back the sum of all the days that have been taken.

Using this resource I tried this aggs but didn't get me the correct result:

{
   "size": 45,
   "filter": {
      "nested": {
         "path": "DaysOff",
         "filter": {
            "range": {
               "DaysOff.Date": {
                  "from": "now-2M",
                  "to": "now"
               }
            }
         }
      }
   },
   "aggs": {
      "sum_docs": {
         "nested": {
            "path": "DaysOff"
         },
         "aggs": {
            "stepped_down": {
               "sum": {
                  "field": "DaysOff.Days"
               }
            }
         }
      }
   }
}

Yeah i have tried using the aggregations but I can't get any data out... — Callum Linington
– Callum Linington, Commented Dec 19, 2015 at 15:28

ChintanShah25 · Accepted Answer · 2015-12-19 16:20:19Z

2

You need to filter on those nested documents to get the correct results, From the docs

Because nested documents are indexed as separate documents, they can only be accessed within the scope of the nested query,

I created index like this

POST employee
{
  "mappings": {
    "emp_map": {
      "properties": {
        "DaysOff": {
          "type": "nested",
          "properties": {
            "Date": {
              "type": "date",
              "format": "strict_date_optional_time||epoch_millis"
            },
            "Days": {
              "type": "double"
            },
            "ID": {
              "type": "long"
            }
          }
        },
        "name": {
          "type": "string"
        }
      }
    }
  }
},

Then I indexed few documents like this,

PUT employee/emp_map/1
{
  "name" : "messi",
  "DaysOff" : [
    {
     "Date" : "2015-11-01",
     "Days" : 1,
     "ID" : 11
    },
    {
     "Date" : "2014-11-01",
     "Days" : 2,
     "ID" : 11
    },
    {
     "Date" : "2015-12-01",
     "Days" : 0.5,
     "ID" : 11
    }
    ]
}

PUT employee/emp_map/2
{
  "name" : "ronaldo",
  "DaysOff" : [
    {
     "Date" : "2015-10-01",
     "Days" : 3,
     "ID" : 12
    },
    {
     "Date" : "2014-11-01",
     "Days" : 2,
     "ID" : 12
    },
    {
     "Date" : "2015-12-01",
     "Days" : 0.5,
     "ID" : 12
    }
    ]
}

PUT employee/emp_map/3
{
  "name" : "suarez",
  "DaysOff" : [
    {
     "Date" : "2015-11-01",
     "Days" : 4,
     "ID" : 13
    },
    {
     "Date" : "2015-11-09",
     "Days" : 2,
     "ID" : 13
    },
    {
     "Date" : "2015-12-01",
     "Days" : 1.5,
     "ID" : 13
    }
    ]
}

This is my query, notice the filter aggregation in nested aggregation, without that ES will give you sum of all the days taken off.

GET employee/_search
{
  "query": {
    "bool": {
      "filter": {
        "nested": {
          "path": "DaysOff",
          "query": {
            "range": {
              "DaysOff.Date": {
                "from": "now-2M",
                "to": "now"
              }
            }
          }
        }
      }
    }
  },
  "aggs": {
    "emp_name": {
      "terms": {
        "field": "name",
        "size": 10
      },
      "aggs": {
        "nesting": {
          "nested": {
            "path": "DaysOff"
          },
          "aggs": {
            "filter_date": {
              "filter": {
                "range": {
                  "DaysOff.Date": {
                    "from": "now-2M",
                    "to": "now"
                  }
                }
              },
              "aggs": {
                "sum_taken_off_days": {
                  "sum": {
                    "field": "DaysOff.Days"
                  }
                }
              }
            }
          }
        }
      }
    }
  },
  "size": 0
}

This is the result I get,

"aggregations": {
    "emp_name": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": "messi",
          "doc_count": 1,
          "nesting": {
            "doc_count": 3,
            "filter_date": {
              "doc_count": 2,
              "sum_taken_off_days": {
                "value": 1.5
              }
            }
          }
        },
        {
          "key": "ronaldo",
          "doc_count": 1,
          "nesting": {
            "doc_count": 3,
            "filter_date": {
              "doc_count": 1,
              "sum_taken_off_days": {
                "value": 0.5
              }
            }
          }
        },
        {
          "key": "suarez",
          "doc_count": 1,
          "nesting": {
            "doc_count": 3,
            "filter_date": {
              "doc_count": 3,
              "sum_taken_off_days": {
                "value": 7.5
              }
            }
          }
        }
      ]
    }
  }

P.S : This is per employee, you can remove emp_name terms aggregation to get sum of all employees.

answered Dec 19, 2015 at 16:20

ChintanShah25

12.7k3 gold badges45 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Callum Linington Over a year ago

Sweet I will have a play around!

Callum Linington Over a year ago

It worked, but what is the point in the first query, if you then filter by the same date stuff in the aggregation?

ChintanShah25 Over a year ago

It will reduce the number of documents on which aggregations need to perform, aggregation is kind of expensive.

Callum Linington Over a year ago

Okay cool. So how would I do a count of the employees in that aggregation. I got avg to work, so I know the average number of days the employees have taken. But now it's good know how many employees I've got coming back

Callum Linington Over a year ago

Actually don't worry about it. I just figured it out :)

Collectives™ on Stack Overflow

ElasticSearch Nested Filter and Aggregation

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related