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.
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.
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
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.
"""SELECT * FROM (employees_nested)
WHERE projects.name LIKE "%sql%"
AND projects.started_year = 1999 """