I have a index with structure that has no. of times a user has loggen into system and is as follows:
[{
"users_id": 5,
"uname": "abcdef",
"status": "active",
"groups_id": 2,
"user_login": [{
"user_logins_id": 12,
"users_id": 5,
"success": "t",
"type": "login",
"date": "2017/01/02",
"ip_address": "198.27.146.70"
},
{
"user_logins_id": 13,
"users_id": 5,
"success": "t",
"type": "logout",
"date": "2017/01/02",
"ip_address": "198.27.146.70"
},
{
"user_logins_id": 12,
"users_id": 5,
"success": "t",
"type": "login",
"date": "2017/01/03",
"ip_address": "198.27.146.70"
},
{
"user_logins_id": 13,
"users_id": 5,
"success": "t",
"type": "logout",
"date": "2017/01/03",
"ip_address": "198.27.146.70"
}
],
"role": "Student"
},
{
"users_id": 2,
"uname": "xyz",
"status": "active",
"groups_id": 1,
"user_login": [{
"user_logins_id": 16,
"users_id": 2,
"success": "t",
"type": "login",
"date": "2017/01/05",
"ip_address": "198.27.146.70"
},
{
"user_logins_id": 17,
"users_id": 5,
"success": "t",
"type": "logout",
"date": "2017/01/06",
"ip_address": "198.27.146.70"
}
],
"role": "Professor"
}
]
Question : Need to know how many times a user of specific role has logged in given date range (day wise results). Solution: I have applied date histogram on user_login.date field (nested document) and terms aggregation on role field which is at root level(using reverse nested aggregation), further i have written a nested aggregation . Date histogram returns sub-buckets more than date range specified.
Following is query i have tried :
{
"size": 0,
"query": {
"bool": {
"must": [
{
"term": {
"status.keyword": {
"value": "active"
}
}
},
{
"nested": {
"path": "user_login",
"query": {
"bool": {
"must": [
{
"range": {
"user_login.date": {
"from": "2017/01/02",
"to": "2017/01/02",
"include_lower": true,
"include_upper": true,
"format": "yyyy/MM/dd",
"boost": 1
}
}
},
{
"match": {
"user_login.type": "login"
}
}
]
}
}
}
}
]
}
},
"aggs": {
"histo": {
"nested": {
"path": "user_login"
},
"aggs": {
"histogrammm": {
"date_histogram": {
"field": "user_login.date",
"interval": "day"
},
"aggs": {
"reverzzzwayyy": {
"reverse_nested": {},
"aggs": {
"roles": {
"terms": {
"field": "role.raw",
"size": 10
},
"aggs": {
"logins1": {
"nested": {
"path": "user_login"
},
"aggs": {
"logins2": {
"filter": {
"bool": {
"must": [
{
"range": {
"user_login.date": {
"from": "2017/01/02",
"to": "2017/01/02",
"include_lower": true,
"include_upper": true,
"format": "yyyy/MM/dd",
"boost": 1
}
}
},
{
"term": {
"user_login.type": {
"value": "login",
"boost": 1
}
}
}
]
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
The above query returns date histogram sub bucket even for "2017/01/03" which is wrong. Any solution to tackle this problem?