Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

[FEATURE] Support JSON format with in-memory processing in V2 engine #1450

Open
acarbonetto opened this issue Mar 17, 2023 · 1 comment
Open
Labels
enhancement New feature or request new-engine-gap

Comments

@acarbonetto
Copy link
Collaborator

acarbonetto commented Mar 17, 2023

Is your feature request related to a problem?
OpenSearch raw response output is output using the JSON format in #1317, but what is not supported are the in-memory calculations. In the legacy engine, this was supported using the painless script calls - but painless is not supported in the new engine.

What solution would you like?
Inject in-memory calls into the raw-JSON OpenSearch response in a similar format that may be used as the legacy output - where in-memory calculations are returned for each row in the fields object, and all other row objects are returned directly from OpenSearch response.

For example, we get both the raw int0 response from OpenSearch in _source, as well as the calculated int0 + 1 column output in fields for each row. null is returned for invalid results (in this example).

$ curl -s -XPOST http://localhost:9200/_plugins/_sql?format=json -H 'Content-Type: application/json' -d '{"query": "SELECT int0 + 1, 2, now() FROM calcs limit 2"}' | jq
{
  "took": 0,
  "timed_out": false,
  "_shards": {
    "total": 1,
    "successful": 1,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": {
      "value": 2,
      "relation": "eq"
    },
    "max_score": 1,
    "hits": [
      {
        "_index": null,
        "_id": null,
        "_score": 0,
        "_source": {
          "int0": 1,
        },
        "fields": {
          "2": 2,
          "int0 + 1": 2,
          "now()": "2023-03-17 13:07:47"
        }
      },
      {
        "_index": null,
        "_id": null,
        "_score": 0,
        "_source": {
          "int0": null,
        },
        "fields": {
          "2": 2,
          "int0 + 1": null,
          "now()": "2023-03-17 13:07:47"
        }
      }
    ]
  }
}

Do we need to include the schema object as well for each of the fields values? It is available.

What alternatives have you considered?
Returning painless script calls are not supported in V2, and adding this feature support just for JSON output is overly complex.

Do you have any additional context?
None

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
enhancement New feature or request new-engine-gap
Projects
None yet
Development

No branches or pull requests

2 participants