Elasticsearch sql response change

Elastic Stack Elasticsearch
1

Hi,

when i use the match query like below:

GET test/_search
{
  "query": {
    "match": {
      "name": "elastic"
    }
  }
}

The response is :

{
  "took" : 2,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 1,
      "relation" : "eq"
    },
    "max_score" : 0.2876821,
    "hits" : [
      {
        "_index" : "test",
        "_type" : "_doc",
        "_id" : "1",
        "_score" : 0.2876821,
        "_source" : {
          "name" : "elastic"
        }
      }
    ]
  }
}

But when i use elasticsearch sql query:

POST /_sql?format=json
{
"query": "SELECT * FROM test "
}

The response comes like below and it is very difficult to read:

{"columns":[{"name":"name","type":"text"}],"rows":[["elastic"]]}

I want to get the json line after line like below:

{
"columns":[
{
"name":"name",
"type":"text"
}
],
"rows":[
["elastic"]]
}

Can you please tell how to get the response like above when we use sql. Beacause it will be helpful for to read. Thanks!

2

Try to add pretty=true

3

Thanks david for the reply.

I tried adding pretty=true. It will not work.
Can you please help me in which way we can do this. Thanks!

4

Just tested it and indeed it does not change the output.
Would you like to open an issue for this?

I did not see that pretty is not supported in the REST API so IMHO it might be either a documentation issue or a real issue we would like to fix.

5

Thanks for your reply David.

Q1 : Is it the issue from elastic side ?

Q2: Is there any other way that the response can be changed? Thanks!

6

Q1: yes

Q2: if you are using curl you can use jq to format it.

7

Thanks david.

Can I know by when the issue will be fixed?

8

Did you open it?

9

No David.

Can i know how to open an issue regarding this. Thanks!

10

Here you can open an issue https://github.com/elastic/elasticsearch

11

No need to create an issue. PR up already: https://github.com/elastic/elasticsearch/pull/43756

12

Thanks Andrei and David.

Can I know in which version this issue has been fixed ?

13

Hi David and Andrei,

Would like to know whether this issue has been addressed in elastic 7.2 version. Thanks!

14

It's not merged so it's not available anywhere yet.

15

Thanks david.

Whether this will available in upcoming version i.e 7.3 ?

16

No idea. Follow the issue and you will see when it's merged in which version it goes.

I'd say that I don't see anything critical here as using pretty is only to help humans to read this content. I agree that's a bug but why do you absolutely need it?

17

David we need to show it to clients. Hence it would be helpful if it comes in a proper json format.

Is it possible to extend any elastic api's and change the resonse accordingly through Java?

18

You can always write your own application which calls elasticsearch endpoint and rewrite whatever you need to whatever format.

I'm curious about what you are doing though.

A client should never read a JSON content. Are you providing something like an API to your users?
And why are you using SQL instead of the _search endpoint?