I need a query that can list an UniqueID that misses data

Elastic Stack Elasticsearch
1

Im new to elasticsearch and i really need some help to get a thing done.

Every user has it own unique ID, ex; 111112,111113,111114.
Every user is connected to a user type, Ex; AA, AB,AC,AD
And every user type has one or more colors, Ex; Blue, Red, Black.

What im trying to achieve is a list of those Uniqie IDs in User type AA that does not have the color red.

(List UniqueID=* UserType=AA Missing=Red)

Is this possible? I know some basic querys, but listing IDs that miss something was a bit hard me and my knowledge.

Its easy in SQL with "Except", but i cant find out how to do it in elasticsearch.

2

There is an exists query which will match a document if the field exists.

If you want the inverse (missing field), you can place the exists query inside a boolean's must_not.

That query can be used in combination with aggregations to get the list that you're interested in. There is also a missing aggregation which can be used to aggregate/collect documents that are missing a field.

1 Like
3

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