Nested/Layered Fields not showing right with CSV

Plugins SQL
1

When I make a csv query, I got the wrong order of columns and further the results don’t include just the value but rather then entire nestled field.
Is there a way to fix this so the output is just like this?
2019-09-02T02:39:59.015-04:00,INFO,dcmitavtol0102

POST _opendistro/_sql?format=csv
{
“query”: “SELECT @timestamp,logstash.log.level,host.name FROM logstash_con_file_6.7.2-2019.36 LIMIT 50”
}

The result is
logstash,@timestamp,host
{log={level=INFO}},2019-09-02T02:39:59.015-04:00,{name=dcmitavtol0102}
{log={level=INFO}},2019-09-02T02:39:59.015-04:00,{name=dcmitavtol0102}
{log={level=INFO}},2019-09-02T02:39:59.015-04:00,{name=dcmitavtol0102}
{log={level=INFO}},2019-09-02T02:39:59.015-04:00,{name=dcmitavtol0102}
{log={level=INFO}},2019-09-02T02:39:59.015-04:00,{name=dcmitavtol0102}
{log={level=INFO}},2019-09-02T02:39:59.015-04:00,{name=dcmitavtol0102}
{log={level=INFO}},2019-09-02T02:39:59.015-04:00,{name=dcmitavtol0102}
{log={level=INFO}},2019-09-02T02:39:59.015-04:00,{name=dcmitavtol0102}
{log={level=INFO}},2019-09-02T02:39:59.015-04:00,{name=dcmitavtol0102}
{log={level=INFO}},2019-09-02T02:39:59.015-04:00,{name=dcmitavtol0102}
{log={level=INFO}},2019-09-02T02:39:59.015-04:00,{name=dcmitavtol0102}

2

Still having issues with 7.2,
has anybody found a solution?

3

Hi, this may be related to issue CSV output when selecting nested items does not format correctly · Issue #285 · opendistro-for-elasticsearch/sql · GitHub. I updated the issue. This seems a bug in CSV format. Our JDBC format (which is default format) can handle this case.

This should be fixed once we merge the separate code paths to a single one: CSV and JDBC export are 2 different execution paths · Issue #148 · opendistro-for-elasticsearch/sql · GitHub.