I'm in a project were we (as the title suggests) are using AWS OpenSearch to fetch data. We have a pretty extensive OpenSearch query and a lot of regular filtering (painless scripts, aggregations etc.) which always need to be used when we query. We just recently discovered the SQL plugin and found it to be of great use in some cases for us. Tho the issue being that we want to combine a regular query with SQL queries to use inner joins for example.
I have looked for documentation regarding this but have found nothing about combining the two forms of queries. I have asked the ever so "trustworthy" ChatGPT but with no success. And now I turn to the old reliable human knowledge to help me in this case.
