[BUG] Generic Client with Custom Deserializer fails to deserialize aggregations

[dsinghal-nice]

What is the bug?
I am using opensearch-java client 2.12.0

Describe the issue:
I have a specific requirement where I will be using aggregations on a index, but I want to use SQL plugin for that. Since the response for the aggregations from OpenSearchClient is very complicated.

I am trying to deserialize the response but getting below error:

Exception in thread "main" jakarta.json.stream.JsonParsingException: Property name 'year' is not in the 'type#name' format. Make sure the request has 'typed_keys' set.

final String body = response.getBody().map(org.opensearch.client.opensearch.generic.Body::bodyAsString).orElse("");

I get the below response as part of Response body

{"took":17,"timed_out":false,"_shards":{"total":5,"successful":5,"skipped":0,"failed":0},"hits":{"total":{"value":1,"relation":"eq"},"max_score":null,"hits":[]},"aggregations":{"year":{"doc_count_error_upper_bound":0,"sum_other_doc_count":0,"buckets":[{"key":1960,"doc_count":1,"COUNT(1)":{"value":0}}]}}}

Below is the code I am trying to deserialize the response

JacksonJsonpMapper jacksonJsonpMapper = new JacksonJsonpMapper();
            final SearchResponse<Book> result = response.getBody().
                    map(b -> Bodies.json(b, SearchResponse.createSearchResponseDeserializer(Book.createBookDeserializer()), jacksonJsonpMapper))
                    .orElse(null);

Below is my code for the same

private static void searchUsingGenericClient(OpenSearchGenericClient openSearchGenericClient) throws IOException {
	JsonObject jsonObject = Json.createObjectBuilder().add("query", "SELECT title, count(1) from books_test where year > 1951 group by year").build();

	try (org.opensearch.client.opensearch.generic.Response response = openSearchGenericClient
			.execute(
					Requests.builder()
							.endpoint("/_plugins/_sql?format=json")
							.method("POST")
							.json(jsonObject)
							.build())) {
		// Retrieve the response body as a simple string
		final String body = response.getBody().map(org.opensearch.client.opensearch.generic.Body::bodyAsString).orElse("");

		JacksonJsonpMapper jacksonJsonpMapper = new JacksonJsonpMapper();
		final SearchResponse<Book> result = response.getBody().
				map(b -> Bodies.json(b, SearchResponse.createSearchResponseDeserializer(Book.createBookDeserializer()), jacksonJsonpMapper))
				.orElse(null);

		System.out.println("object conversion from generic client :: " + result.hits().hits().get(0).source());
	}
	System.out.println("End searchUsingGenericClient :: ");
}

Do you have any additional context?
(https://forum.opensearch.org/t/generic-client-with-custom-deserializer-fails-to-deserialize-aggregations/20739)

[dblock]

[Weekly Catch All Triage - 1]

[Swiddis]

@opensearch-project/admin can we transfer this to opensearch-java client?

[ykmr1224]

@opensearch-project/admin reminder to take a look

[Xtansia]

Copying my response from the forum here for posterity:

The aggregation deserialization logic usually relies on passing typed_keys to search requests to be able to unambiguously
deserialize the different types of aggregation, but it looks like the SQL plugin doesn’t support passing that parameter.

---

@Swiddis @ykmr1224 Is my understanding correct that there's currently no way to pass typed_keys into the SQL/PPL query? If not how feasible is it to be added?