Complex queries - LEFT JOIN ON WHERE NULL issue

SQL
#1

Hello, i ask for your help with complex queries,

data:
Two indices in elasticsearch

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

Approach:
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):

grafik
grafik802×624 23 KB

Problem:
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: https://opendistro.github.io/for-elasticsearch-docs/docs/sql/complex/

Thank you in advance!

Jonas

#2

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!