# Is there any solution to do the “NOT IN” functionality in Elasticsearch?
**URL:** https://discuss.elastic.co/t/is-there-any-solution-to-do-the-not-in-functionality-in-elasticsearch/21520
**Category:** Elasticsearch
**Created:** [January 7, 2015, 10:43am UTC](https://discuss.elastic.co/t/is-there-any-solution-to-do-the-not-in-functionality-in-elasticsearch/21520 "2015-01-07T10:43:26Z")
**Posts on this page:** 5
**Page:** 1
### Author:  [@Hosang\_Jeon](https://discuss.elastic.co/u/Hosang_Jeon)
#### Post date: [January 7, 2015, 10:43am UTC](https://discuss.elastic.co/t/is-there-any-solution-to-do-the-not-in-functionality-in-elasticsearch/21520/1 "2015-01-07T10:43:26Z")
Here is a simple but difficult question. I want to do an aggregation for a
query results that should be use\*"NOT IN"\* functionality like any RDBMS'
SQL.
For example, I want to do a job something like below.
curl -XGET [http://localhost:9200/my\_index/my\_type/\_search?pretty](http://localhost:9200/my_index/my_type/_search?pretty) -d '{
"query": {
"filtered": {
"filter": {
!!! Documents whose 'user\_id' field value is 'NOT IN' distinct user\_ids where the 'action' field value is 'signup' !!!
}
}
},
"aggregations": {
"distinct\_users":{
"cardinality": {
"field": "user\_id",
"precision\_threshold": 1000000
}
}
}
}'
Is it possible to get the results what I want in Elasticsearch?
Thanks in advance.
--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/4aa88ff2-e0ae-45f3-85f4-9c6afba3842c%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/4aa88ff2-e0ae-45f3-85f4-9c6afba3842c%40googlegroups.com).
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
---
### Author:  [@jpountz](https://discuss.elastic.co/u/jpountz)
#### Post date: [January 7, 2015, 11:01am UTC](https://discuss.elastic.co/t/is-there-any-solution-to-do-the-not-in-functionality-in-elasticsearch/21520/2 "2015-01-07T11:01:17Z")
It depends how your documents are modeled. If both the action and user id
fields are in the same document, then this could be a simple not\>term
filter, but I'm afraid that what you are looking for is a join, which
elasticsearch does not support (well, it does, but only specific cases via
parent/child and nested docs, not general-purpose joins).
On Wed, Jan 7, 2015 at 11:43 AM, Ho-sang Jeon [jhsbeat@gmail.com](mailto:jhsbeat@gmail.com) wrote:
> Here is a simple but difficult question. I want to do an aggregation for a
> query results that should be use\*"NOT IN"\* functionality like any RDBMS'
> SQL.
>
> For example, I want to do a job something like below.
>
> curl -XGET [http://localhost:9200/my\_index/my\_type/\_search?pretty](http://localhost:9200/my_index/my_type/_search?pretty) -d '{
> "query": {
> "filtered": {
> "filter": {
> !!! Documents whose 'user\_id' field value is 'NOT IN' distinct user\_ids where the 'action' field value is 'signup' !!!
> }
> }
> },
> "aggregations": {
> "distinct\_users":{
> "cardinality": {
> "field": "user\_id",
> "precision\_threshold": 1000000
> }
> }
> }
> }'
>
> Is it possible to get the results what I want in Elasticsearch?
>
> Thanks in advance.
>
> --
> You received this message because you are subscribed to the Google Groups
> "elasticsearch" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).
> To view this discussion on the web visit
> [https://groups.google.com/d/msgid/elasticsearch/4aa88ff2-e0ae-45f3-85f4-9c6afba3842c%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/4aa88ff2-e0ae-45f3-85f4-9c6afba3842c%40googlegroups.com)
> [https://groups.google.com/d/msgid/elasticsearch/4aa88ff2-e0ae-45f3-85f4-9c6afba3842c%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/4aa88ff2-e0ae-45f3-85f4-9c6afba3842c%40googlegroups.com?utm_medium=email&utm_source=footer)
> .
> For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
--
Adrien Grand
--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CAL6Z4j6VeJZG\_2qgs9H6TZpD2du8rRuy-G%2BYTBi6C4cc4uPPmw%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAL6Z4j6VeJZG_2qgs9H6TZpD2du8rRuy-G%2BYTBi6C4cc4uPPmw%40mail.gmail.com).
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
---
### Author:  [@Hosang\_Jeon](https://discuss.elastic.co/u/Hosang_Jeon)
#### Post date: [January 7, 2015, 11:18am UTC](https://discuss.elastic.co/t/is-there-any-solution-to-do-the-not-in-functionality-in-elasticsearch/21520/3 "2015-01-07T11:18:34Z")
> Thanks Andrien Grand.
To clarify my quesion, I have added some example data below.
Here is an example data.
curl -s -XPOST 'localhost:9200/my\_index/my\_type/1' -d'{ "user\_id": 1234, "action": "signup" }'
curl -s -XPOST 'localhost:9200/my\_index/my\_type/2' -d'{ "user\_id": 1234, "action": "visit" }'
curl -s -XPOST 'localhost:9200/my\_index/my\_type/3' -d'{ "user\_id": 1234, "action": "visit" }'
curl -s -XPOST 'localhost:9200/my\_index/my\_type/4' -d'{ "user\_id": 5678, "action": "visit" }'
curl -s -XPOST 'localhost:9200/my\_index/my\_type/5' -d'{ "user\_id": 5678, "action": "visit" }'
curl -s -XPOST 'localhost:9200/my\_index/my\_type/6' -d'{ "user\_id": 9012, "action": "signup" }'
curl -s -XPOST 'localhost:9200/my\_index/my\_type/7' -d'{ "user\_id": 9012, "action": "visit" }'
curl -s -XPOST 'localhost:9200/my\_index/my\_type/8' -d'{ "user\_id": 9012, "action": "visit" }'
curl -s -XPOST 'localhost:9200/my\_index/my\_type/9' -d'{ "user\_id": 3456, "action": "visit" }'
curl -s -XPOST 'localhost:9200/my\_index/my\_type/10' -d'{ "user\_id": 3456, "action": "visit" }'
What I really want to get is the "Documents whose user\_id DOES NOT signed
up based on these log data". So, documents [_4, 5, 9, 10_] are the final
results what I want to get.
Is it possible to get the results what I want in Elasticsearch?
Thanks in advance.
--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/c58d06b0-e775-4c35-be6a-40e9ea8c82cf%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/c58d06b0-e775-4c35-be6a-40e9ea8c82cf%40googlegroups.com).
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
---
### Author:  [@ES\_USER1](https://discuss.elastic.co/u/ES_USER1)
#### Post date: [January 8, 2015, 1:10pm UTC](https://discuss.elastic.co/t/is-there-any-solution-to-do-the-not-in-functionality-in-elasticsearch/21520/4 "2015-01-08T13:10:18Z")
Not sure this really helps you but it might be easier and more reliable of
a search to do this as two separate queries the first would just be an agg
listing all distinct users and the second an agg listing users who have an
action of "signup"? and then just subtracting that list from the first.
On Wednesday, January 7, 2015 6:18:34 AM UTC-5, Ho-sang Jeon wrote:
> Thanks Andrien Grand.
>
> >
>
> To clarify my quesion, I have added some example data below.
>
> Here is an example data.
>
> curl -s -XPOST 'localhost:9200/my\_index/my\_type/1' -d'{ "user\_id": 1234, "action": "signup" }'
> curl -s -XPOST 'localhost:9200/my\_index/my\_type/2' -d'{ "user\_id": 1234, "action": "visit" }'
> curl -s -XPOST 'localhost:9200/my\_index/my\_type/3' -d'{ "user\_id": 1234, "action": "visit" }'
> curl -s -XPOST 'localhost:9200/my\_index/my\_type/4' -d'{ "user\_id": 5678, "action": "visit" }'
> curl -s -XPOST 'localhost:9200/my\_index/my\_type/5' -d'{ "user\_id": 5678, "action": "visit" }'
> curl -s -XPOST 'localhost:9200/my\_index/my\_type/6' -d'{ "user\_id": 9012, "action": "signup" }'
> curl -s -XPOST 'localhost:9200/my\_index/my\_type/7' -d'{ "user\_id": 9012, "action": "visit" }'
> curl -s -XPOST 'localhost:9200/my\_index/my\_type/8' -d'{ "user\_id": 9012, "action": "visit" }'
> curl -s -XPOST 'localhost:9200/my\_index/my\_type/9' -d'{ "user\_id": 3456, "action": "visit" }'
> curl -s -XPOST 'localhost:9200/my\_index/my\_type/10' -d'{ "user\_id": 3456, "action": "visit" }'
>
> What I really want to get is the "Documents whose user\_id DOES NOT signed
> up based on these log data". So, documents [_4, 5, 9, 10_] are the final
> results what I want to get.
>
> Is it possible to get the results what I want in Elasticsearch?
> Thanks in advance.
--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/1cf8f178-08de-4e8b-943e-73f3e5ce8042%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/1cf8f178-08de-4e8b-943e-73f3e5ce8042%40googlegroups.com).
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [July 6, 2017, 12:40am UTC](https://discuss.elastic.co/t/is-there-any-solution-to-do-the-not-in-functionality-in-elasticsearch/21520/5 "2017-07-06T00:40:03Z")