[FEATURE] can't modify response format in SQL plugin (JDBC to json)

[archipelweb]

Is your feature request related to a problem?

In a query in SQL, we wan't to get the internal document id (key : _id) and his index (key : _index) but the default format response (JDBC) don't return this keys We get schema and values

In the official documentation i found this : https://opensearch.org/docs/latest/search-plugins/sql/response-formats I want to use the json format.

In SQL plugin, we can't modify the default response format If we try to put the format in parameters we have this message :

OpenSearch\\Common\\Exceptions\\UnexpectedValueException: \"format\" is not a valid parameter. Allowed parameters are \"error_trace\", \"filter_path\", \"human\", \"opaqueId\", \"pretty\", \"source\" in /vendor/opensearch-project/opensearch-php/src/OpenSearch/Endpoints/AbstractEndpoint.php on line 250

What solution would you like?

Add the support of this parameter format in allowable parameters for SQL Plugin

In src/OpenSearch/Endpoints/Sql/Query.php

Change the method

 public function getParamWhitelist(): array
    {
        return [];
    }

to :

 public function getParamWhitelist(): array
    {
        return [
            'format'
        ];
    }

I tested this solution by physically changing this file in the vendor directory it's good for me

What alternatives have you considered?

If this issue don't succeded, we can made a fork but this solution is overkill :D

Do you have any additional context?

I hope I have been as clear as possible

[shyim]

Hey,

feel free to make a PR :)

[archipelweb]

Hi, Do you have a date for the next release?

[dblock]

Can we close this with https://github.com/opensearch-project/opensearch-php/pull/161?

@archipelweb Feel free to open an issue like "Please release v.xyz." and everyone should add some +1s, we'll be happy to do a release quickly as needed.

[goran-popovic]

Hi, can this be released? Regarding my particular use case, it's interfering with Laravel Scout since it expects "hits" to be present in the results, and the "hits" are present when using the JSON format only.

I believe this can be closed with #161.

@dblock Should I create an issue that says "Please release v2.2.1"? Should it be a proposal? I don't see that being done before, so I'm not sure about the format of the issue.

[dblock]

@goran-popovic Yes, please create the issue, @shyim I can try to make a release (or you can if you have time), any objections?

[dblock]

Closing via https://github.com/opensearch-project/opensearch-php/pull/161.

[goran-popovic]

@dblock here it is #181. Please address it when able.