Elasticsearch group by multiple fields and sum the hours (aggregation)

Saurabh

I am working on an elasticsearch(1.5) query to get all the tasks for users and their respective hours within a time interval. For example, 1st Jan 2016 - 31 Dec 2016.

This is what I have managed to get so far:

{
  "query": {
    "filtered": {
      "query" : {
        "bool" : {
          "must":
          {
            "term": {
              "userId": [1,2,3,4,5,6]
            }
          }
        }
      },
      "filter": {
        "range": {
          "spentOn": {
          "gte": "1451606400000", // 1st Jan 
          "lte": "1483142400000" // 31st Dec
          }
        }
      }
    }
  },
  "size":0,
  "aggs": {
    "group_by_interval": {
      "date_histogram": {
        "field": "spentOn",
        "interval": "month",
        "min_doc_count": 0,
        "extended_bounds": {
          "min": "1451606400000", 
          "max": "1483142400000"
        }
      },
      "aggs": {
        "group_per_project": {
          "histogram": {
            "field": "taskId",
            "interval": 1
          },

          "aggs": {
            "hours": {
              "sum": {
                "field": "hours"

              }
            }
          }
        }
      }
    }
  }
}

The above query gives me the following result:

{
...
[{
        "key_as_string" : "2016-01-01T00:00:00.000Z",
        "doc_count" : 10,
        "group_per_project" : {
            "buckets" : [{
                    "doc_count" : 1,
                    "key" : Task A,
                    "hours_per_taskAssignment" : {
                        "value" : 5
                    }
                }, {
                    "doc_count" : 15,
                    "key" : Task  B,
                    "hours_per_taskAssignment" : {
                        "value" : 60
                    }
                }, {
                    "doc_count" : 1,
                    "key" :Task  C,
                    "hours_per_taskAssignment" : {
                        "value" : 10
                    }
                }
            ]
        },
        "key" : 1451606400000
    }, {
        "key_as_string" : "2016-02-01T00:00:00.000Z",
        "doc_count" : 23,
        "group_per_project" : {
            "buckets" : [{
                    "doc_count" : 1,
                    "key" : Task A,
                    "hours" : {
                        "value" : 2
                    }
                }, {
                    "doc_count" : 20,
                    "key" : Task B,
                    "hours" : {
                        "value" : 180
                    }
                }
            ]
        },
        "key" : 1454284800000
    }
...
]

However, I need the hours to be grouped and summed by the user, instead of a summation of all the user's hours. For example in January:

{
 "doc_count" : 2,
 "key" : Task A,
 {
    "userId": 1
    "hours": {"value": 2}
 },
  {
    "userId": 2
    "hours": {"value": 5}
 }
}

Is there any way I can achieve the above result using ElasticSearch 1.5 without having to loop through each individual user and get the total hours and in doing so reducing the performance of the application?

Thanks in advance!

Andrei Stefan
  "aggs": {
    "group_by_interval": {
      "date_histogram": {
        "field": "spentOn",
        "interval": "month",
        "min_doc_count": 0,
        "extended_bounds": {
          "min": "1451606400000",
          "max": "1483142400000"
        }
      },
      "aggs": {
        "group_per_project": {
          "histogram": {
            "field": "taskId",
            "interval": 1
          },
          "aggs": {
            "per_user": {
              "terms": {
                "field": "userId"
              },
              "aggs": {
                "hours": {
                  "sum": {
                    "field": "hours"
                  }
                }
              }
            }
          }
        }
      }
    }
  }

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

ElasticSearch group by multiple fields

From Dev

Aggregation $group $sum fields among one document

From Dev

Elasticsearch - Aggregation on multiple fields in the same nested scope

From Dev

Mongo aggregation in java: group with multiple fields

From Dev

mongodb aggregation get all fields sum group by date

From Dev

Sum the total hours and group by hours

From Java

Group multiple times in aggregation framework keeping multiple fields

From Dev

Elasticsearch aggregation by 7 fields

From Dev

elasticsearch terms and sum aggregation

From Dev

ElasticSearch range in sum aggregation

From Java

Java stream group by and sum multiple fields

From Dev

Linq query with GROUP BY on multiple fields, SUM and COUNT

From Dev

Java stream group by and sum multiple fields

From Dev

Linq query with GROUP BY on multiple fields, SUM and COUNT

From Dev

Elasticsearch how count values from multiple document fields using aggregation

From Dev

Elasticsearch how count values from multiple document fields using aggregation

From Dev

ElasticSearch aggregation - filter and group by

From Dev

Aggregation / Group By w/ a Sum

From Dev

Aggregation and Sum with one Group By

From Dev

Aggregation - Unwind multiple fields

From Dev

Group and sum worked hours by days

From Dev

Group and sum worked hours by days

From Dev

MongoDB: Aggregation, group by several fields

From Dev

elasticsearch aggregation group by null key

From Dev

aggregation with $group stage and $sum operator

From Dev

aggregation with $group stage and $sum operator

From Dev

mongo $sum compounded when doing $unwind and then $group on multiple fields

From Dev

using $group to sum fields from multiple sub documents in mongodb

From Dev

ActiveRecord: complex query with Sum, Join, Group By and Select multiple fields

Related Related

  1. 1

    ElasticSearch group by multiple fields

  2. 2

    Aggregation $group $sum fields among one document

  3. 3

    Elasticsearch - Aggregation on multiple fields in the same nested scope

  4. 4

    Mongo aggregation in java: group with multiple fields

  5. 5

    mongodb aggregation get all fields sum group by date

  6. 6

    Sum the total hours and group by hours

  7. 7

    Group multiple times in aggregation framework keeping multiple fields

  8. 8

    Elasticsearch aggregation by 7 fields

  9. 9

    elasticsearch terms and sum aggregation

  10. 10

    ElasticSearch range in sum aggregation

  11. 11

    Java stream group by and sum multiple fields

  12. 12

    Linq query with GROUP BY on multiple fields, SUM and COUNT

  13. 13

    Java stream group by and sum multiple fields

  14. 14

    Linq query with GROUP BY on multiple fields, SUM and COUNT

  15. 15

    Elasticsearch how count values from multiple document fields using aggregation

  16. 16

    Elasticsearch how count values from multiple document fields using aggregation

  17. 17

    ElasticSearch aggregation - filter and group by

  18. 18

    Aggregation / Group By w/ a Sum

  19. 19

    Aggregation and Sum with one Group By

  20. 20

    Aggregation - Unwind multiple fields

  21. 21

    Group and sum worked hours by days

  22. 22

    Group and sum worked hours by days

  23. 23

    MongoDB: Aggregation, group by several fields

  24. 24

    elasticsearch aggregation group by null key

  25. 25

    aggregation with $group stage and $sum operator

  26. 26

    aggregation with $group stage and $sum operator

  27. 27

    mongo $sum compounded when doing $unwind and then $group on multiple fields

  28. 28

    using $group to sum fields from multiple sub documents in mongodb

  29. 29

    ActiveRecord: complex query with Sum, Join, Group By and Select multiple fields

HotTag

Archive