SQL "where not exists" equivalent for Elastic


I have a set of stages I am logging. A GUID is on each record to tie the stage together.

Guid Stage
1 Stage 1
1 Stage 2
1 Stage 3
1 Stage 4
1 Stage 5
2 Stage 1
2 Stage 2
2 Stage 3
2 Stage 4
2 Stage 5

I am looking to put a visual together on any GUID which has any missing stages.
In SQL I would use something along the lines of:

select * from tbl as a
where stage = 1 and
not exists (
select * from tbl as b where
stage = 5 and a.guid = b.guid

Any advice on how to achieve this in elastic/kibana?

You can use the exists to filter for fields that do not have values defined for a field.

You can use the "Add filter" UI to create a does not exist filter.

Hi Nathan,

Thanks for replying to my query.
For my example, there are no missing fields within each document. Each log has GUID and a Stage. Rather, I'm looking for a method of searching for documents where a corresponding document does not exist.
So for example If I have 3 logs:
GUID Stage
1 1
1 2
2 1

I'm looking for a way of reporting that GUID 2 does not have logs for all stages.

Any advice you can help me with?


This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.