I think that you nailed it, Elasticsearch JDBC is not flexible and you need to be good in SQL if you have a complex model with many relation. Because JOIN create a cartesian product you can end with duplicate and you have to find a way to avoid that because duplicate affect the ranking. In my case I ended using UNION queries.

Also you cannot also have multiple queries but not to update and existing document(eg: adding extra fields) The SQL query for me ended being ~100 lines but the model is quite complex with multiple languages support.