Hello all,
I have a SQL statement as below:
select min(a.datetime),accname,year from CWLC_PC a group by accname,year;
I have tried many times using agg and max but I cannot get the result as the SQL statement.
Please help!
Thank you.
Try this:
{
"aggs": {
"accname": {
"terms": {
"field": "accname"
},
"aggs": {
"year ": {
"terms": {
"field": "year "
},
"aggs" : {
"min_date" : { "min" : { "field" : "datetime" } }
}
}
}
}
}
}Hello wang,
I have tried your suggestion, but the problem is the result didn't narrow down. What I want is like the SQL statement, the result will actually only showing the min(date) for the particular accname, for example,
datetime, accname
2018-09-01 abc
2018-09-02 abc
The query result will be
datetime, accname
2018-09-01 abc
I think the result will narrow down.
Post test2/_search
{
"aggs": {
"accname": {
"terms": {
"field": "accname.keyword"
},
"aggs": {
"min_date": {
"min": {
"field": "datetime"
}
}
}
}
}
}
The result is :
"aggregations": {
"accname": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": "abc",
"doc_count": 2,
"min_date": {
"value": 1535760000000,
"value_as_string": "2018-09-01T00:00:00.000Z"
}
}
]
}
}Hello wang,
After looking at your result, I think it is my elasticsearch problem
I tried to do a simple query like
GET /monthly-data/_search
{
"query": {
"match_all": {}
},
"aggregations": {
"accname": {
"terms": {
"field": "User.keyword"
}
}
}
}
in my result, it still shows like
{
"took" : 15,
"timed_out" : false,
"_shards" : {
"total" : 5,
"successful" : 5,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : 339769,
"max_score" : 1.0,
"hits" : [
{.....................
But do not have keywords like "aggregation", do you know is that a bug for my version 6.5.4?
No, the result contains two segments: "hits" and "aggregation" . The "aggregation" segement is in the bottom of result. You can ignore search hits by setting size=0.
Post test2/search
{
"size": 0,
"aggs": {
"accname": {
"terms": {
"field": "accname.keyword"
},
"aggs": {
"min_date": {
"min": {
"field": "datetime"
}
}
}
}
}
}
the complete result :
{
"took": 1010,
"timed_out": false,
"_shards": {
"total": 5,
"successful": 5,
"skipped": 0,
"failed": 0
},
"hits": {
"total": 2,
"max_score": 0,
"hits": [
]
},
"aggregations": {
"accname": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": "abc",
"doc_count": 2,
"min_date": {
"value": 1535760000000,
"value_as_string": "2018-09-01T00:00:00.000Z"
}
}
]
}
}
}I see wang,
Thanks for your help,
but how can I show all the aggs result? I only see a few aggs results,
Also, can I use the aggs result to visualize in kibana? how should i configure to visualize the aggs result but not the hits result?
Yes, the Terms Aggregation doesn't return all results. It returns only the top 20 buckets(maybe 10 buckets, I am not sure) . There are many you should notice about Terms Aggregation, you can refer:https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-terms-aggregation.html
The Kibana supports visualize the aggs result.
but I cannot find min aggregation at where you point at, is that my version 6.5.4 too old?
sorry that it is kibana question though
I could tell you my goal is that I want to have a cumulative sum overtime on the base on the aggs result, I think I don't want the Y-Axis to be the min(datetime) but cumulative sum instead. Do you know how to do it in one go? Anyway Thanks in answering Kibana questions.
You can post it into Kibana category to get more help
OK Thanks.
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.