Retrieve the original raw document via SQL

[PeterS]

Is it possible to get the original raw document via SQL? E.g.:

SELECT _source FROM index WHERE indexedField = ...

Currently, I have to map arrays as “nested” type and use self-joins to access objects in arrays. With raw document I can get arrays from Json directly.

[andrewc]

Interesting idea.

You can make a request to add this feature, here
This would similar to how other meta-fields from OpenSearch work

Alternatively, you can consider using the format=jsonoutput format to get the raw output from OpenSearch.

[andrewc]

Also worth noting: nested objects often return in the inner_hits field when inner_hits are requested (such as in the SELECT clause). So it may not be good enough to retrieve the _source values.

[PeterS]

Thank you for your hints.

jdbc format seems to return only 1st item from arrays whereas json returns whole arrays.

Interesting that I can get similar results also in jdbc when I wrap table name into brackets.

POST _plugins/_sql?format=jdbc
{
  "query": 
    """SELECT * FROM (employees_nested)
         WHERE projects.name LIKE "%sql%"
           AND projects.started_year = 1999 """
}