I try to make aggregation query with ElasticSearch 6.8:
I want to find last documents for specific date, and last documents before specific date, group by documents fields.
curl -X PUT "http://localhost:9200/test/xxx/1" -H 'Content-Type: application/json' -d'{"c1" : "1","c2": "1-1","ts": "2020-01-01T06:00:00.000+0000", "rec_type": "t1"}'
curl -X PUT "http://localhost:9200/test/xxx/2" -H 'Content-Type: application/json' -d'{"c1" : "1","c2": "1-2","ts": "2020-01-02T06:00:00.000+0000", "rec_type": "t1"}'
curl -X PUT "http://localhost:9200/test/xxx/3" -H 'Content-Type: application/json' -d'{"c1" : "1","c2": "1-3","ts": "2020-03-16T06:00:00.000+0000", "rec_type": "t1"}'
curl -X PUT "http://localhost:9200/test/xxx/4" -H 'Content-Type: application/json' -d'{"c1" : "1","c2": "1-4","ts": "2020-03-16T09:00:00.000+0000", "rec_type": "t1"}'
curl -X PUT "http://localhost:9200/test/xxx/5" -H 'Content-Type: application/json' -d'{"c1" : "2","c2": "2-1","ts": "2020-01-01T06:00:00.000+0000", "rec_type": "t1"}'
curl -X PUT "http://localhost:9200/test/xxx/6" -H 'Content-Type: application/json' -d'{"c1" : "2","c2": "2-2","ts": "2020-01-02T06:00:00.000+0000", "rec_type": "t1"}'
curl -X PUT "http://localhost:9200/test/xxx/7" -H 'Content-Type: application/json' -d'{"c1" : "2","c2": "2-3","ts": "2020-03-16T06:00:00.000+0000", "rec_type": "t1"}'
curl -X PUT "http://localhost:9200/test/xxx/8" -H 'Content-Type: application/json' -d'{"c1" : "2","c2": "2-4","ts": "2020-03-16T09:00:00.000+0000", "rec_type": "t1"}'
curl -X PUT "http://localhost:9200/test/_mapping/_doc" -H 'Content-Type: application/json' -d'{"properties" : {"c2": {"type": "text", "fielddata": true}}}'
curl -X PUT "http://localhost:9200/test/_mapping/_doc" -H 'Content-Type: application/json' -d'{"properties" : {"ts": {"type": "date"}}}'
My data:
| c1 | c2 | ts | rec_type |
|---|---|---|---|
| 1 | 1-1 | 2020-01-01T06:00:00.000+0000 | t1 |
| 1 | 1-2 | 2020-01-02T06:00:00.000+0000 | t1 |
| 1 | 1-3 | 2020-03-16T06:00:00.000+0000 | t1 |
| 1 | 1-4 | 2020-03-16T09:00:00.000+0000 | t1 |
| 2 | 2-1 | 2020-01-01T06:00:00.000+0000 | t1 |
| 2 | 2-2 | 2020-01-02T06:00:00.000+0000 | t1 |
| 2 | 2-3 | 2020-03-16T06:00:00.000+0000 | t1 |
| 2 | 2-4 | 2020-03-16T09:00:00.000+0000 | t1 |
and I can get records 1-4 and 2-4, but also i want to get 1-2 and 2-2
My query:
{
"size":0,
"query":{
"bool":{
"must":[
{
"bool":{
"must":[
{
"term":{
"rec_type.keyword":{
"value":"t1",
"boost":1.0
}
}
},
{
"range":{
"ts":{
"from":"2020-03-16T00:00:00.000+0000",
"to":"2020-03-16T23:59:59.000+0000",
"include_lower":true,
"include_upper":true,
"boost":1.0
}
}
}
],
"adjust_pure_negative":true,
"boost":1.0
}
}
],
"adjust_pure_negative":true,
"boost":1.0
}
},
"aggregations":{
"last_records":{
"composite":{
"size":100,
"sources":[
{
"record":{
"terms":{
"field":"c1.keyword",
"order":"asc"
}
}
}
]
},
"aggregations":{
"top_hits":{
"top_hits":{
"from":0,
"size":1,
"version":false,
"explain":false,
"sort":[
{
"ts":{
"order":"desc"
}
},
{
"c2":{
"order":"desc"
}
}
]
}
}
}
}
}
}
How i can get records 1-3 and 2-3?
Thank you!