Create a visualization or filter that shows data from one query, but where some data are exclude based on another query (Left Join)

[runewage (Rune Waage)]

Hi
In my data in Kibana I have one filed referring to another field. (xref -> yref). What I want to achieve is to have a visualization showing me all data where someField = someData, except the records where xref is within another query providing all records where someField=someOtherData

In SQL it would have been something like this:
Select from table A where a=b AND xref not in (select yref from table A where a=c)

I hope someone can help.

I have tried to use "span_not" and "include"/"exclude", but it does not work. I also tried a simple query using "span_term" which gave me no data. while the same query using "term" gives me data in return.

I am very new to Kibana (which you might already suspected). Unfortunately I have not found anything in the documentation so far to solve this use case.

Br,
Rune Waage

[cheiligers]

@runewage welcome to the community!
It would help to know what type of visualization are you trying to create (there are several in Kibana) and what the shape of your documents is. You should be able to use a combination of filters to get the visualization you need. Using your example from above, you'd create a visualization that shows you where "someField = someData" and then use a filter to filter out where "someField=someOtherData" (i.e add a filter for "someField" is not "someOtherData". I hope that helps!