Use Date Histogram with Two Periods

[TomTom]

Is it possible to use aggregations with date histogram to return multiple groups without having to perform the same search multiple times?

Example:

Day of Week	Week 1	Week 2
Monday	120	125
Tuesday	110	100
Wednesday	200	220
Thursday	320	200
Friday	150	123
Saturday	1234	1231
Sunday	435	123

I was only able to do the grouping for a week, with the code below:

{
    "size": 0,
    "query": {
        "bool": {
            "must": [
                {
                    "bool": {
                        "must": [
                            {
                                "term": {
                                    "account_id": {
                                        "value": 1234
                                    }
                                }
                            },
                            {
                                "term": {
                                    "finished": {
                                        "value": "Y"
                                    }
                                }
                            }
                        ]
                    }
                },
                {
                    "range": {
                        "date_of_creation": {
                            "from": "2019-09-10T00:00:00-0000",
                            "to": "2019-09-24T23:59:59-0000",
                            "include_lower": true,
                            "include_upper": true
                        }
                    }
                }
            ]
        }
    },
    "_source": false,
    "stored_fields": "_none_",
    "aggregations": {
        "group": {
            "date_histogram": {
                "field": "date_of_creation",
                "interval": "day",
                "format": "e"
            }
        }
    }
}

[spinscale (Alexander Reelsen)]

I think the correct way here would be to have a root level aggregation, that aggregates on the day of the week, which can be done with a terms aggregation using a script like return doc['datetime'].value.getDayOfWeek(); - and then have a nested aggregation that is a date histogram based on weeks - with some hope thatI got your requirement right

Hope this helps.