[FEATURE] Command-line option to output JSON response

[MaxKsyunz]

Is your feature request related to a problem?
When I use sli-cli in command-line mode (i.e., opensearchsql -p '<query> sometime I would like to get the raw JSON output from the SQL plugin so that I can post process it with other tools.

What solution would you like?
A command-line option, like --json that in conjunction with -q option makes sql-cli output JSON documents.

What alternatives have you considered?
Using OpenSearch SQL REST API directly. This gets very repetitive after a while.

[joshuali925]

it does support --format json, but the output doesn't seem like real json...

❯ opensearchsql -q 'select * from accounts limit 1' --format json
{'took': 1, 'timed_out': False, '_shards': {'total': 1, 'successful': 1, 'skipped': 0, 'failed': 0}, 'hits': {'total': {'value': 1000, 'relation': 'eq'}, 'max_score': 1.0, 'hits': [{'_index': 'accounts', '_id': '1', '_score': 1.0, '_source': {'account_number': 1, 'balance': 39225, 'firstname': 'Amber', 'lastname': 'Duke', 'age': 32, 'gender': 'M', 'address': '880 Holmes Lane', 'employer': 'Pyrami', 'email': 'amberduke@pyrami.com', 'city': 'Brogan', 'state': 'IL'}}]}}

it would need additional formatting to make it valid json which is not good

❯ opensearchsql -q 'select * from accounts limit 1' --format json | node -e 'var False = false, True = true; console.log(JSON.stringify(eval("(" + require("fs").readFileSync(0).toString() + ")"), null, 2))'
{
  "took": 0,
  "timed_out": false,
  "_shards": {
    "total": 1,
    "successful": 1,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": {
      "value": 1000,
      "relation": "eq"
    },
    "max_score": 1,
    "hits": [
      {
        "_index": "accounts",
        "_id": "1",
        "_score": 1,
        "_source": {
          "account_number": 1,
          "balance": 39225,
          "firstname": "Amber",
          "lastname": "Duke",
          "age": 32,
          "gender": "M",
          "address": "880 Holmes Lane",
          "employer": "Pyrami",
          "email": "amberduke@pyrami.com",
          "city": "Brogan",
          "state": "IL"
        }
      }
    ]
  }
}

I'll bring up to the team but not sure when we'll get to feature requests in sql-cli. Also curious why is sql-cli non-interactive mode less repetitive than curl?

[Yury-Fridlyand]

opensearchsql -q 'select * from account limit 1' --format json | sed -e "s/'/\"/g" | sed -e s/False/false/g | sed -e s/True/true/g | jq

[joshuali925]

opensearchsql -q 'select * from account limit 1' --format json | sed -e "s/'/\"/g" | sed -e s/False/false/g | sed -e s/True/true/g | jq

sed could break when values have single quote ' (or True/False) in them..

[MaxKsyunz]

Ah! Thank you @joshuali925 and @Yury-Fridlyand. opensearchsql --help does not list JSON as an option, it's also not quite what I'm looking for.

Setting ?format=json on the REST call would route the query to the legacy engine and I'm interested in results from the new engine.

@joshuali925 I understand sql-cli is a pretty low priority tool. But it's open source -- a PR might appear.

As for curl, is there a way to make it assume an end point? I basically want <tool> -q '<SQL STATEMENT> syntax and get a well-formed JSON back. I can get close with httpie as http :9200/_plugin/_sql query='<SQL STATEMENT> but I still have to type out the endpoint. I could script it but then I'll just be re-implementing sql-cli.

[joshuali925]

if by json you meant json output vs the ascii table output, not sql engine json vs jdbc format, then you can do something like

sudo tee /usr/local/bin/sql >/dev/null <<'EOF'
#!/usr/bin/env bash
curl -X POST -k -H 'Content-Type: application/json' "${ENDPOINT:-localhost:9200/_plugins/_sql}" -d "$(jq -n --arg query "$*" '$ARGS.named')"
EOF
sudo chmod +x /usr/local/bin/sql

# using it
sql 'select * from accounts limit 1'
ENDPOINT='https://admin:admin@remote/_plugins/_sql' sql 'select * from accounts limit 1'

For httpie you can change the curl line to http "${ENDPOINT:-localhost:9200/_plugins/_sql}" query="$*"

Setting ?format=json on the REST call would route the query to the legacy engine and I'm interested in results from the new engine.

If this is the concern and you meant to also support ?format=json in new engine then probably should also create an issue in sql repo