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.
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=json
output format to get the raw output from OpenSearch.
1 Like
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.
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 """
}