Using ES query in Spring Boot


(Daniel Alvarez) #1

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:

  1. 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?
  1. 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.


(David Pilato) #2
  1. 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?

The former. Note that if you use the High Level Rest Client, it exposes lot of helpers to build that from Java.

I have a project example here:

You can read more about this example here: http://david.pilato.fr/blog/2015/05/09/advanced-search-for-your-legacy-application/

  1. 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?

If you don't need to do special transformations with your data but just display the result on a page, indeed sending the JSON response as is back to the front end is the easiest way IMO.

  • Is it possible to create a complex POJO and marshall ES JSON to that POJO?

You can use Jackson for that.


(Daniel Alvarez) #3

Hi @dadoonet.

Thank your for your response.

That example will be a great help.

Regards.


(system) #4

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.