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!
"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.
Comments