Hi all.
I have a Spring Boot project with several functionalities.
It uses Spring Data Elasticsearch to store documents in Elasticsearch node.
And it also works as a REST Api for other applications.
One of this applications must show a datatable (with Angular) with some data we get from Elasticsearch (ES from now).
We have correctly developed everything, ES is working fine and we have made a query in Kibana that returns all data we need for this datatable.
Here are some code snippets of ES document, query and returned data.
ES document:
{
"_index" : "ticket_stadistics_index",
"_type" : "ticket_stadistics",
"_id" : "38760",
"_score" : 1.0,
"_source" : {
"calledOut" : "2017-10-05T10:19:17.000Z",
"issueTime" : "2017-10-05T10:06:05.000Z",
"visitorMobile" : "xxxxxxxxx",
"visitorEmail" : null,
"resolution" : 1,
"visitorName" : "XXXXX",
"@timestamp" : "2018-03-01T11:48:25.049Z",
"name" : "F001",
"@version" : "1",
"id" : 38760,
"servicePoint" : {
"name" : "Cliente - Tu Experto (sólo con cita)",
"id" : 8,
"serviceTime" : 300
},
"waitTime" : 673,
"queue" : {
"name" : "433 - CARTAGENA",
"id" : 35
},
"calledIn" : "2017-10-05T10:17:18.000Z",
"status" : 3
}
}
Kibana query:
GET ticket_stadistics_index/_search
{
"from": 0,
"size": 0,
"query": {
"range": {
"issueTime": {
"gte": "2018-01-01T00:00:00",
"lte": "2018-01-31T23:59:59"
}
}
},
"aggs": {
"group_by_queue": {
"terms": {
"field": "queue.name.keyword"
},
"aggs": {
"group_by_point": {
"terms": {
"field": "servicePoint.name.keyword"
},
"aggs": {
"sp_tickets_data": {
"filters": {
"filters": {
"llegados": {
"match_all": {}
},
"atendidos": {
"bool": {
"must": {
"term": {
"status": 3
}
}
}
},
"noPresentados": {
"bool": {
"must": {
"term": {
"resolution": 1
}
},
...
"queue_tickets_data": {
"filters": {
"filters": {
"llegados": {
"match_all": {}
},
"atendidos": {
"bool": {
"must": {
"term": {
"status": 3
}
}
}
},
"noPresentados": {
"bool": {
"must": {
"term": {
"resolution": 1
}
},
...
(I can provide hole query if needed)
And here one of the response hits:
"aggregations": {
"group_by_queue": {
"doc_count_error_upper_bound": 1136,
"sum_other_doc_count": 52621,
"buckets": [
{
"key": "494 -STORE",
"doc_count": 3963,
"queue_agg_att_times": {
"doc_count": 3808,
"sumAttTime": {
"value": 2138941000
},
"maxWaitTime": {
"value": 10964000
},
"mediaAttTime": {
"value": 561696.6911764706
},
"maxAttTime": {
"value": 22596000
},
"waitMediaTime": {
"value": 749615.0210084034
}
},
"group_by_point": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 31,
"buckets": [
{
"key": "Cliente - Info comercial - Particulares",
"doc_count": 2231,
"sp_tickets_data": {
"buckets": {
"<NS": {
"doc_count": 1058
},
">NS": {
"doc_count": 1072
},
"atendidos": {
"doc_count": 2130
},
"llegados": {
"doc_count": 2231
},
"noPresentados": {
"doc_count": 483
}
}
}
As you can see, we are saving tickets from queues, and then returning some totals grouped by queue and in each queue grouped by service point.
We are facing 2 problems:
- We have to translate this complex and big query to JavaAPI. Which is the best way to accomplish this?
- Start with the first filter and then add aggregations and subaggregations to it?
- Start with the deepest subaggregation and then wrapp it in the upper level?
- As you can see, JSON response does not have anything to do with document saved. We have no idea how to return this data to Java.
- Is it better to return plain JSON and work with it in Angular?
- Is it possible to create a complex POJO and marshall ES JSON to that POJO?
Any help will be appreciated.
Thank you very much.