[FEATURE] Support raw OpenSearch output in JSON format in V2 engine

[acarbonetto]

Is your feature request related to a problem?
Several IT tests only run against Legacy engine because the V2 engine does not support JSON output format.

Update: Several IT test use the painless sprint to process in-memory requests that are not easily inserted into the raw JSON output in V2/new engine because V2/new engine doesn't support passing painless script calls.

A proposal to solve this will be included in #1450

What solution would you like?
Various output formats are supported by the SQL engine, including RAW, CSV, JSON, and JDBC. However, only JSON format is not supported by the V2 engine. Whenever json format is provided, the system automatically falls back to the legacy engine. This means V2 engine support (such as SELECT 1) functions cannot return json format, but does work for JDBC, RAW, and CSV.

curl --location --request POST 'http://localhost:9200/_plugins/_sql?format=json' \
--header 'Content-Type: application/json' \
--data-raw '{
    "query": "SELECT 1"
}'

What alternatives have you considered?
JSON format output is only supported by the legacy engine.

Do you have any additional context?
Add any other context or screenshots about the feature request here.