Filter documents based on array comparison (matches ALL entries)

Elastic Stack Elasticsearch
1

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.
For more options, visit https://groups.google.com/groups/opt_out.

2

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.
For more options, visit https://groups.google.com/groups/opt_out.

3

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

--Alex

On Tue, Jul 2, 2013 at 3:42 PM, David p 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.
For more options, visit 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.
For more options, visit https://groups.google.com/groups/opt_out.