Is it possible to pass simple SQL queries tested in console in Elastic Search Java Client?

38 Views Asked by At

I have a query that I tested in the elastic search console as follows

POST /_sql
 {
   "query": """
SELECT YEAR(dateOnSite), building.type, COUNT(*) as count  FROM "allndexessample*" GROUP BY YEAR(dateOnSite), building.type ORDER BY YEAR(dateOnSite) ASC
   """
 }

This retrives counts of records that are grouped by the year and building type from all my indexes in my database.

Sample response from the console,

{
  "columns" : [
    {
      "name" : "YEAR(dateOnSite)",
      "type" : "integer"
    },
    {
      "name" : "building.type",
      "type" : "text"
    },
    {
      "name" : "count",
      "type" : "long"
    }
  ],
  "rows" : [
    [
      1922,
      "COLLECTIVE",
      1
    ],
    [
      2020,
      "COLLECTIVE",
      4
    ],
    [
      2022,
      "COLLECTIVE",
      21
    ],
    [
      2022,
      "INDIVIDUEL",
      38
    ],
    [
      2023,
      "COLLECTIVE",
      22
    ],
    [
      2023,
      "INDIVIDUEL",
      4
    ]
  ],
  "cursor" : "some long value"
}

The actual JSON data that is stored in elastic search is huge with multiple data, but retrieving this whole data is not something I am looking for.

I am currently using elasticsearch-java with version 8.1.2

This retrieves the complete json object that matches the critera with a limit of 10 records, but I am unsure on how to retrive the data as counts using this method

    ElasticsearchClient client
    String searchText = "COLLECTIVE";
        SearchResponse<Product> response;
        try {
            response = client.search(s -> s
                    .index("allndexessample*")
                    .query(q -> q
                        .match(t -> t
                            .field("building.type")
                            .query(searchText)
                        )
                    ),
                VentilationControlData.class);
        } catch (Exception r) {
            throw new IOException(" Exception", r);
        }
        return response;

Can anyone point in the right direction on how I should use the search in elasticClient to retirve the data I am looking for?

0

There are 0 best solutions below