Improve the REST API response format

[rmoff]

CLI v5.4.0-beta190925193823, Server v5.4.0-beta190925193823

A REST call for a SELECT currently returns a body like this:

[
  {
    "@type": "rows",
    "statementText": "SELECT FULL_NAME, NUM_RATINGS FROM RATINGS_BY_USER WHERE ROWKEY ='BOB Brockherst';",
    "schema": "`ROWKEY` STRING KEY, `FULL_NAME` STRING, `NUM_RATINGS` BIGINT",
    "rows": [
      [
        "BOB Brockherst",
        "BOB Brockherst",
        694
      ]
    ],
    "warnings": []
  }
]

It would be useful if there were an option to return it in a more malleable format, in which the schema and rows were both more accessible.

At the moment it would be necessary to manually parse the schema value

`ROWKEY` STRING KEY, `FULL_NAME` STRING, `NUM_RATINGS` BIGINT

and from that associate the numbered entries of the rows array with the given field name

 [
        "BOB Brockherst",
        "BOB Brockherst",
        694
      ]

Suggestion - Schema

Instead of a single string lump, make each field its own object:

{
    "schema": [
        {
            "field_name": "ROWKEY",
            "data_type": "STRING",
            "is_key": "true"
        },
        {
            "field_name": "FULL_NAME",
            "data_type": "STRING"
        },
        {
            "field_name": "NUM_RATINGS",
            "data_type": "BIGINT"
        }
    ]
}

Suggestion - Values

Include the field name in the object so that it can be more easily accessed programatically:

{
    "rows": [
        {
            "ROWKEY": "BOB Brockherst",
            "FULL_NAME": "BOB Brockherst",
            "NUM_RATINGS": 694
        }
    ]
}

[rmoff]

Related: #1343

[rmoff]

I think this would be something we'd want to have as an option (like other REST APIs offer ?pretty etc) because otherwise rows could get unnecessarily big from repeating the field names each time.

[big-andy-coates]

Can't say I'm a fan personally.

I originally had a more JSON like structure to defining the columns. However, we're an SQL engine and hence I think returning an SQL schema is more correct.

Embedding the column names in each returned row is just noise and payload bloat IMHO.

[rmoff]

@big-andy-coates I definitely agree this wouldn't be wanted all the time, and hence a flag in returning it would be useful.
WDYT about splitting the schema out into a structure as suggested above too?

[big-andy-coates]

WDYT about splitting the schema out into a structure as suggested above too?

I refer the right honourable gentleman to my last comment:

I originally had a more JSON like structure to defining the columns. However, we're an SQL engine and hence I think returning an SQL schema is more correct.

I definitely agree this wouldn't be wanted all the time, and hence a flag in returning it would be useful.

Like we don't already have enough to do? ;) :p

In all honesty, an issue such as this is unlikely to get any engineering time unless there is a lot of pressure/interest from users. On the face of it, it's a pretty low ROI task. Of course, that's just my honest opinion.

[rmoff]

See https://stackoverflow.com/questions/62131541/parsing-json-returned-with-schema-element-in-java for an example where the current API causes problems for end users.

[rmoff]

Related: https://stackoverflow.com/questions/64121225/ksqldb-rest-api-return-query-results-as-json/

[rmoff]

/cc @MichaelDrogalis @derekjn any thoughts on this?