[FEATURE] BI tools compliance with complex objects

[Yury-Fridlyand]

Is your feature request related to a problem?

Opensearch and SQL plugin use dot notation to delimit fields and subfields for nested objects. BI tools (Power BI and Tableau Desktop) don't use DESCRIBE request to get list of columns[^1].

Example

Part of ecommerce index mapping (sample data set from Dashboards).

{
  "mappings" : {
    "properties" : {
      "geoip" : {
        "properties" : {
          "city_name" : {
            "type" : "keyword"
          },
          "continent_name" : {
            "type" : "keyword"
          },
          "country_iso_code" : {
            "type" : "keyword"
          },
          "location" : {
            "type" : "geo_point"
          },
          "region_name" : {
            "type" : "keyword"
          }
        }
      }
    }
  }
}

DESCRIBE response:	Table	Column	Other info
ecommerce	geoip	...
ecommerce	geoip.city_name	...
ecommerce	geoip.continent_name	...
ecommerce	geoip.country_iso_code	...
ecommerce	geoip.location	...
ecommerce	geoip.region_name	...

SELECT * response:	geoip
...data...

(there are no geoip.something columns)

What solution would you like?

Add new SQL plugin setting with boolean value. By default it is deactivated (unset). Once set, it will enforce expanding all complex objects in select * query.

What alternatives have you considered?

JDBC and ODBC drivers could be updated by having new connection string parameter. Once set, driver will send new request parameter

Do you have any additional context?

Add any other context or screenshots about the feature request here.

[^1]: PBI runs select * from ... limit 0, Tableau runs select * from ... where 1 = 0.

[Yury-Fridlyand]

https://github.com/opensearch-project/sql/blob/9fbcf11258964616d2f5056420cc83afedd71613/core/src/main/java/org/opensearch/sql/analysis/symbol/SymbolTable.java#L119-L128 When SymbolTable::lookupAllFields patched to return allSymbols, Tableau and PBI can operate with <field>.<subfield> columns.

[GumpacG]

Be aware of nested objects as it may unexpectedly return nulls.

[acarbonetto]

@GumpacG nested type is out of scope for this issue. Please create a separate issue to discuss the nested type as it may require a different solution.