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

**URL:** <https://discuss.elastic.co/t/i-need-a-query-that-can-list-an-uniqueid-that-misses-data/155690>\
**Category:** Elasticsearch\
**Created:** [November 7, 2018, 9:01am UTC](https://discuss.elastic.co/t/i-need-a-query-that-can-list-an-uniqueid-that-misses-data/155690 "2018-11-07T09:01:12Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![d\_g](https://avatars.discourse-cdn.com/v4/letter/d/779978/32.png) [@d\_g](https://discuss.elastic.co/u/d_g)\
**Post date:** [November 7, 2018, 9:01am UTC](https://discuss.elastic.co/t/i-need-a-query-that-can-list-an-uniqueid-that-misses-data/155690/1 "2018-11-07T09:01:12Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![polyfractal](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/polyfractal/32/48162_2.png) [@polyfractal](https://discuss.elastic.co/u/polyfractal)\
**Post date:** [November 12, 2018, 3:45pm UTC](https://discuss.elastic.co/t/i-need-a-query-that-can-list-an-uniqueid-that-misses-data/155690/2 "2018-11-12T15:45:30Z")

</div>

There is an [exists query](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-exists-query.html) 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](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-missing-aggregation.html) which can be used to aggregate/collect documents that are missing a field.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [December 10, 2018, 3:45pm UTC](https://discuss.elastic.co/t/i-need-a-query-that-can-list-an-uniqueid-that-misses-data/155690/3 "2018-12-10T15:45:35Z")

</div>

Closed on December 10, 2018, 3:45pm UTC

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