Complex queries - LEFT JOIN ON WHERE NULL issue

Hello, i ask for your help with complex queries,

Two indices in elasticsearch

I want to select all gateway_names within one index that are not existent in the other.

SELECT * FROM index_large a LEFT JOIN index_small e ON (a.gateway_name = e.gateway_name) WHERE index_small .id is NULL

Similiar to (just the filter addition of all the null values):

I do not retain all null-values, but all values. (The filter doesn’t seem to work)

Has someone a different approach or an idea what is wrong with mine?

Documentation of complex queries:

Thank you in advance!


Hi @Jonas , we do have this issue for post-filtering/aggregation after JOIN. The current impl will push down the WHERE conditions to the DSL queries on each side. Here is the issue related for your reference: LEFT JOIN with WHERE doesn't filter the result as expected · Issue #124 · opendistro-for-elasticsearch/sql · GitHub. A complete support for JOIN will be added later. Thanks!