Need help in complicate aggregation with ElasticSearch query

Viewed 70

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"}}}'

Data looks like:

c1 c2 ts rec_type
1 1-1 2020-01-01T06:00:00 t1
1 1-2 2020-01-02T06:00:00 t1
1 1-3 2020-03-16T06:00:00 t1
1 1-4 2020-03-16T09:00:00 t1
2 2-1 2020-01-01T06:00:00 t1
2 2-2 2020-01-02T06:00:00 t1
2 2-3 2020-03-16T06:00:00 t1
2 2-4 2020-03-16T09:00:00 t1

My query returns only last record for specific date (1-4 and 2-4 in the table above, but I want to take in same bucket a 1-2 and 2-2 ):

{
    "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"
                                }
                            }
                        ]
                    }
                }
            }
        }
    }
}

So question, is it even possible, and how ?

0 Answers
Related