Skip to content

Instantly share code, notes, and snippets.

@glenacota
Created January 31, 2020 23:09
Show Gist options
  • Save glenacota/47a0f9a563c4028032c0debbc1d557df to your computer and use it in GitHub Desktop.
Save glenacota/47a0f9a563c4028032c0debbc1d557df to your computer and use it in GitHub Desktop.
# Based on the query pusblished in the question, I added the following documents to the index.
[
{
"@timestamp" : "2020-01-15",
"condition" : "B",
"value" : 10,
"conditionType" : "ABCD"
},
{
"@timestamp" : "2020-01-15",
"condition" : "B",
"value" : 50,
"conditionType" : "ABCD"
},
{
"@timestamp" : "2020-01-15",
"condition" : "A",
"value" : 50,
"conditionType" : "ABCD"
},
{
"@timestamp" : "2020-01-18",
"condition" : "B",
"value" : 50,
"conditionType" : "ABCD"
},
{
"@timestamp" : "2020-01-18",
"condition" : "B",
"value" : 150,
"conditionType" : "ABCD"
},
{
"@timestamp" : "2020-01-19",
"condition" : "B",
"value" : 10,
"conditionType" : "ABCD"
},
{
"@timestamp" : "2020-01-19",
"condition" : "A",
"value" : 20,
"conditionType" : "ABCD"
}
]
# Here is the query. The differences w.r.t. the one in the question are that (1) I dropped the range
# query on the @timestamp because not relevant; (2) the term filter is on the `condition.keyword` subfield
# (because term-level filters operate only on non-analysed datatypes) and the value is capitalized (because
# the term-level query is case sensitive); (3) I added the `group_by_values` as in my posted solution.
{
"size": 0,
"query": {
"bool": {
"must": [
{
"match": {
"conditionType": "ABCD"
}
}
]
}
},
"_source": "userData",
"aggs": {
"student_data": {
"date_histogram": {
"field": "@timestamp",
"calendar_interval": "day"
},
"aggs": {
"condition_B": {
"filter": {
"term": {
"condition.keyword": "B"
}
},
"aggs": {
"group_by_values": {
"terms": {
"field": "value",
"size": 100
}
},
"user_avg": {
"avg": {
"field": "value"
}
}
}
}
}
}
}
}
# This is the result, which is as expected.
"aggregations" : {
"student_data" : {
"buckets" : [
{
"key_as_string" : "2020-01-15T00:00:00.000Z",
"key" : 1579046400000,
"doc_count" : 3,
"condition_B" : {
"doc_count" : 2,
"group_by_values" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : 10,
"doc_count" : 1
},
{
"key" : 50,
"doc_count" : 1
}
]
},
"user_avg" : {
"value" : 30.0
}
}
},
{
"key_as_string" : "2020-01-16T00:00:00.000Z",
"key" : 1579132800000,
"doc_count" : 0,
"condition_B" : {
"doc_count" : 0,
"group_by_values" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [ ]
},
"user_avg" : {
"value" : null
}
}
},
{
"key_as_string" : "2020-01-17T00:00:00.000Z",
"key" : 1579219200000,
"doc_count" : 0,
"condition_B" : {
"doc_count" : 0,
"group_by_values" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [ ]
},
"user_avg" : {
"value" : null
}
}
},
{
"key_as_string" : "2020-01-18T00:00:00.000Z",
"key" : 1579305600000,
"doc_count" : 2,
"condition_B" : {
"doc_count" : 2,
"group_by_values" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : 50,
"doc_count" : 1
},
{
"key" : 150,
"doc_count" : 1
}
]
},
"user_avg" : {
"value" : 100.0
}
}
},
{
"key_as_string" : "2020-01-19T00:00:00.000Z",
"key" : 1579392000000,
"doc_count" : 2,
"condition_B" : {
"doc_count" : 1,
"group_by_values" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : 10,
"doc_count" : 1
}
]
},
"user_avg" : {
"value" : 10.0
}
}
}
]
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment