# Filter documents based on array comparison (matches ALL entries)
**URL:** https://discuss.elastic.co/t/filter-documents-based-on-array-comparison-matches-all-entries/12587
**Category:** Elasticsearch
**Created:** [June 29, 2013, 6:01pm UTC](https://discuss.elastic.co/t/filter-documents-based-on-array-comparison-matches-all-entries/12587 "2013-06-29T18:01:18Z")
**Posts on this page:** 4
**Page:** 1
### Author:  [@David\_p](https://discuss.elastic.co/u/David_p)
#### Post date: [June 29, 2013, 6:01pm UTC](https://discuss.elastic.co/t/filter-documents-based-on-array-comparison-matches-all-entries/12587/1 "2013-06-29T18:01:18Z")
Hi,
I want to filter out documents when the search does not contain all ids of
nested documents.
Example:
Product has many Groups
User has many Groups
An user should only get products, if he has all the groups specified in
Product.
Product.groups = [1,2,3]; User.groups = [1,2,3] # =\> User can get the
product
Product.groups = [1,2,3]; User.groups = [2,3] # =\> User should not be able
to get the product
I tried it with nested documents:
Indexes:
curl -XPUT localhost:9200/products/product/1 -d '{
"title": "Title Product 1",
"groups": [
{ "id": 2 },
{ "id": 3 }
]
}'
curl -XPUT localhost:9200/products/product/2 -d '{
"title": "Title Product 2",
"groups": [
{ "id": 8 },
{ "id": 12 }
]
}'
Mapping:
curl -XPUT localhost:9200/products/product/\_mapping -d '{
"prodcut":{
"properties":{
"groups": {
"type":"nested"
}
}
}
}'
Search:
curl -XGET localhost:9200/products/product/\_search -d '{
"query":{
"filtered":{
"query":{"match\_all":{}},
"filter": {
"nested": {
"path": "groups",
"query":{
"filtered": {
"query": { "match\_all": {}},
"filter": {
"terms": { "groups.id": [3] }
}
}
}
}
}
}
}
}'
This search will return Product 1, even though the filter terms does not
contain all group ids that are set in the product. The filter only checks
if there is any id present, but not if _all_ ids are present (I also tried
it with the "execution: 'and'" option and wrapping terms in "and").
Is there a way to specify a filter which checks if all ids are present? In
the search above, it should only return the product if the groups.id in
terms contains the ids 2 and 3.
Thanks in advance!
David
--
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).
For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out).
---
### Author:  [@David\_p](https://discuss.elastic.co/u/David_p)
#### Post date: [July 2, 2013, 1:42pm UTC](https://discuss.elastic.co/t/filter-documents-based-on-array-comparison-matches-all-entries/12587/2 "2013-07-02T13:42:14Z")
Any hints?
Am Samstag, 29. Juni 2013 20:01:18 UTC+2 schrieb David p:
> Hi,
>
> I want to filter out documents when the search does not contain all ids of
> nested documents.
>
> Example:
> Product has many Groups
> User has many Groups
>
> An user should only get products, if he has all the groups specified in
> Product.
>
> Product.groups = [1,2,3]; User.groups = [1,2,3] # =\> User can get the
> product
> Product.groups = [1,2,3]; User.groups = [2,3] # =\> User should not be able
> to get the product
>
> I tried it with nested documents:
>
> Indexes:
>
> curl -XPUT localhost:9200/products/product/1 -d '{
> "title": "Title Product 1",
> "groups": [
> { "id": 2 },
> { "id": 3 }
> ]
> }'
>
> curl -XPUT localhost:9200/products/product/2 -d '{
> "title": "Title Product 2",
> "groups": [
> { "id": 8 },
> { "id": 12 }
> ]
> }'
>
> Mapping:
>
> curl -XPUT localhost:9200/products/product/\_mapping -d '{
> "prodcut":{
> "properties":{
> "groups": {
> "type":"nested"
> }
> }
> }
> }'
>
> Search:
>
> curl -XGET localhost:9200/products/product/\_search -d '{
> "query":{
> "filtered":{
> "query":{"match\_all":{}},
> "filter": {
> "nested": {
> "path": "groups",
> "query":{
> "filtered": {
> "query": { "match\_all": {}},
> "filter": {
> "terms": { "groups.id": [3] }
> }
> }
> }
> }
> }
> }
> }
> }'
>
> This search will return Product 1, even though the filter terms does not
> contain all group ids that are set in the product. The filter only checks
> if there is any id present, but not if _all_ ids are present (I also tried
> it with the "execution: 'and'" option and wrapping terms in "and").
>
> Is there a way to specify a filter which checks if all ids are present? In
> the search above, it should only return the product if the groups.id in
> terms contains the ids 2 and 3.
>
> Thanks in advance!
>
> David
--
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).
For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out).
---
### Author:  [@spinscale](https://discuss.elastic.co/u/spinscale)
#### Post date: [July 3, 2013, 7:48am UTC](https://discuss.elastic.co/t/filter-documents-based-on-array-comparison-matches-all-entries/12587/3 "2013-07-03T07:48:27Z")
Hey,
you _could_ add the 'include\_in\_root' setting in your nested mapping and
then use a script filter which checks that the size of the groups.id list
is the same than in your query, but I highly doubt that this is a good idea
(increasing your index size, hard to maintain).
Think of the filter statement as a where statement. So when you have a
where statement in SQL filtering for id=3, you do not automatically filter
out this document, if you have another relation with id=2, except you
explicitely specify this in another where query. This is the analogue for
elasticsearch queries.
If you can, you should maybe model your data differently in order to make
your queries more simple. Or modify your queries so they are 'complete'
(include all the filters you really want). I guess the terms filter lookup
can help you a lot in cases likes this. See
> **[Elasticsearch Platform — Find real-time answers at scale](https://www.elastic.co)**
>
> Power insights and outcomes with the Elasticsearch Platform and AI. See into your data and find answers that matter with enterprise solutions designed to help you build, observe, and protect. Try Elasticsearch free today.
--Alex
On Tue, Jul 2, 2013 at 3:42 PM, David p [ownside@gmx.de](mailto:ownside@gmx.de) wrote:
> Any hints?
>
> Am Samstag, 29. Juni 2013 20:01:18 UTC+2 schrieb David p:
>
> > Hi,
> >
> > I want to filter out documents when the search does not contain all ids
> > of nested documents.
> >
> > Example:
> > Product has many Groups
> > User has many Groups
> >
> > An user should only get products, if he has all the groups specified in
> > Product.
> >
> > Product.groups = [1,2,3]; User.groups = [1,2,3] # =\> User can get the
> > product
> > Product.groups = [1,2,3]; User.groups = [2,3] # =\> User should not be
> > able to get the product
> >
> > I tried it with nested documents:
> >
> > Indexes:
> >
> > curl -XPUT localhost:9200/products/\*\*product/1 -d '{
> > "title": "Title Product 1",
> > "groups": [
> > { "id": 2 },
> > { "id": 3 }
> > ]
> > }'
> >
> > curl -XPUT localhost:9200/products/\*\*product/2 -d '{
> > "title": "Title Product 2",
> > "groups": [
> > { "id": 8 },
> > { "id": 12 }
> > ]
> > }'
> >
> > Mapping:
> >
> > curl -XPUT localhost:9200/products/\*\*product/\_mapping -d '{
> > "prodcut":{
> > "properties":{
> > "groups": {
> > "type":"nested"
> > }
> > }
> > }
> > }'
> >
> > Search:
> >
> > curl -XGET localhost:9200/products/\*\*product/\_search -d '{
> > "query":{
> > "filtered":{
> > "query":{"match\_all":{}},
> > "filter": {
> > "nested": {
> > "path": "groups",
> > "query":{
> > "filtered": {
> > "query": { "match\_all": {}},
> > "filter": {
> > "terms": { "groups.id": [3] }
> > }
> > }
> > }
> > }
> > }
> > }
> > }
> > }'
> >
> > This search will return Product 1, even though the filter terms does not
> > contain all group ids that are set in the product. The filter only checks
> > if there is any id present, but not if _all_ ids are present (I also tried
> > it with the "execution: 'and'" option and wrapping terms in "and").
> >
> > Is there a way to specify a filter which checks if all ids are present?
> > In the search above, it should only return the product if the groups.idin terms contains the ids 2 and 3.
> >
> > Thanks in advance!
> >
> > David
>
> --
> 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).
> For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out).
--
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).
For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out).
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [July 6, 2017, 2:28am UTC](https://discuss.elastic.co/t/filter-documents-based-on-array-comparison-matches-all-entries/12587/4 "2017-07-06T02:28:29Z")