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:
- Get all rows from given date range (entry_datetime field) - this query is simple.
- Group results by event_id - I can use aggregation here.
- Get all entry_datetime values from every group, make a list/array and get 3 earliest dates. - Is it possible?
- Get all entry_price values and return the smallest value - again, aggregation.
- 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:
- How can I collect data from the third point?
- 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.