Get given number of aggregations and merge values to a single array

[eshlox]

Hi,

I have an index with mapping like this:

{
    "index-name": {
        "mappings": {
            "my_search": {
                "dynamic": "strict",
                "properties": {
                    "event_category_id": {
                        "type": "integer"
                    },
                    "event_id": {
                        "type": "keyword"
                    },
                    "event_priority": {
                        "type": "integer"
                    },
                    "event_enabled": {
                        "type": "boolean"
                    },
                    "entry_id": {
                        "type": "keyword"
                    },
                    "entry_datetime": {
                        "type": "date"
                    },
                    "entry_price": {
                        "type": "integer"
                    },
                }
            }
        }
    }
}

Example data:

event_id	event_category_id	event_priority	event_enabled	entry_id	entry_datetime	entry_price
1e2c93	1	2	true	199ck2	2017-12-16T20:00:00+00:00	100
1e2c93	1	2	true	299ck2	2017-12-18T20:00:00+00:00	60
1e2c93	1	2	false	399ck2	2017-12-21T20:00:00+00:00	40
1e2c93	1	2	true	499ck2	2017-12-23T20:00:00+00:00	100
2e2c93	1	2	true	599ck2	2017-12-17T20:00:00+00:00	10
2e2c93	1	2	true	699ck2	2017-12-20T20:00:00+00:00	30
3e2c93	1	2	true	799ck2	2017-12-23T20:00:00+00:00	90
3e2c93	1	2	true	899ck2	2017-12-24T20:00:00+00:00	90
3e2c93	1	2	true	998ck2	2017-12-25T20:00:00+00:00	90
4e2c93	1	2	true	999ck2	2017-12-22T20:00:00+00:00	100

It looks like NoSQL table with denormalized data. Each row has an unique entry_id, but event_id may repeat.

I would like to get a given number of events (unique) and data from entries. Every event has a lot of other fields which are the same for every event with given ID (not visible in example data) so I can get the first event and get the data from it.

I need a response which should look like this:

{
    {
        'event_id': '1e2c93',
        'event_category_id': 1,
        'event_priority': 2,
        'dates': ['2017-12-16T20:00:00+00:00', '2017-12-18T20:00:00+00:00', '2017-12-23T20:00:00+00:00'],
        'min_price': 60
    },
    {
        'event_id': '3e2c93',
        'event_category_id': 1,
        'event_priority': 2,
        'dates': ['2017-12-16T23:00:00+00:00', '2017-12-18T24:00:00+00:00', '2017-12-21T25:00:00+00:00'],
        'min_price': 30
    }
}

How I understand this:

1. Get all rows from given date range (entry_datetime field) - this query is simple.
2. Group results by event_id - I can use aggregation here.
3. Get all entry_datetime values from every group, make a list/array and get 3 earliest dates. - Is it possible?
4. Get all entry_price values and return the smallest value - again, aggregation.
5. The rest of data can be copied from a random event with given id - I found that I could use Top hits aggregation, looks promising.

My questions:

1. How can I collect data from the third point?
2. How can I get a given number of grouped events? (I want to use also pagination.) I can't probably get data from given date range (point 1) and then group it because it can result in a smaller number of groups (events) that I would want to get.

Any hints or ideas?

Thanks.