SQL: selecting fields of type object or nested

[bpintea]

Currently, selecting a nested or an object field will fail the query.
This is problematic with some BI tools, since these try to offer the user a preview of the data. This is done:

• right after the connection is made and available tables listed to the user, if they simply clicking on any of the listed tables that contain such a column (/index field), in preparation to load the data from it;
• before the user gets a chance to see the columns in the table and be given the choice of which ones to load.

This leads to the user generally not being able to use the tool right away. Workarounds exist, but they would require the user to find out the list and types of the columns and construct an SQL query manually to avoid the problematic fields.

Potential solutions:

• not listing the nested and object columns, if the client is of certain type (ODBC); but still allow SELECT'ing subfields, if explicitly requested.
• listing these columns, but not failing the request, by maybe returning:
  • NULL for these fields, if directly selected;
  • a binary/base64 blob for object;
  • a JSON for nested, stringified up to a certain default level.

[elasticmachine]

Pinging @elastic/es-search

[astefan]

master (7.0.0): 908c8de
6.x (6.7.0): 423b3b0
6.6 (6.6.1): 754b70c