{"action":"created","issue":{"url":"https://api.github.com/repos/elastic/elasticsearch/issues/2674","labels_url":"https://api.github.com/repos/elastic/elasticsearch/issues/2674/labels{/name}","comments_url":"https://api.github.com/repos/elastic/elasticsearch/issues/2674/comments","events_url":"https://api.github.com/repos/elastic/elasticsearch/issues/2674/events","html_url":"https://github.com/elastic/elasticsearch/issues/2674","id":11284603,"number":2674,"title":"Query DSL: Terms filter to allow for terms lookup from another document","user":{"login":"kimchy","id":41300,"avatar_url":"https://avatars.githubusercontent.com/u/41300?v=3","gravatar_id":"","url":"https://api.github.com/users/kimchy","html_url":"https://github.com/kimchy","followers_url":"https://api.github.com/users/kimchy/followers","following_url":"https://api.github.com/users/kimchy/following{/other_user}","gists_url":"https://api.github.com/users/kimchy/gists{/gist_id}","starred_url":"https://api.github.com/users/kimchy/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/kimchy/subscriptions","organizations_url":"https://api.github.com/users/kimchy/orgs","repos_url":"https://api.github.com/users/kimchy/repos","events_url":"https://api.github.com/users/kimchy/events{/privacy}","received_events_url":"https://api.github.com/users/kimchy/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/elastic/elasticsearch/labels/feature","name":"feature","color":"328a58"},{"url":"https://api.github.com/repos/elastic/elasticsearch/labels/v0.90.0.Beta1","name":"v0.90.0.Beta1","color":"DDDDDD"}],"state":"closed","locked":false,"assignee":null,"milestone":null,"comments":9,"created_at":"2013-02-22T12:55:26Z","updated_at":"2015-04-25T18:09:16Z","closed_at":"2013-02-22T13:04:16Z","body":"The terms filter requires providing all the terms as part of the filter itself. Allow to automatically extract them from an external document.\r\n\r\nHere is an example:\r\n\r\n```\r\n# index the information for user with id 2, specifically, its friends\r\ncurl -XPUT localhost:9200/users/user/2 -d '{\r\n   \"friends\" : [\"1\", \"3\"]\r\n}'\r\n\r\n# index a tweet, from user with id 2\r\ncurl -XPUT localhost:9200/tweets/tweet/1 -d '{\r\n   \"user\" : \"2\"\r\n}'\r\n\r\n# search on all the tweets that match the friends of user 2\r\ncurl -XGET localhost:9200/tweets/_search -d '{\r\n  \"query\" : {\r\n    \"filtered\" : {\r\n        \"filter\" : {\r\n            \"terms\" : {\r\n                \"user\" : {\r\n                    \"index\" : \"users\",\r\n                    \"type\" : \"user\",\r\n                    \"id\" : \"2\",\r\n                    \"path\" : \"friends\"\r\n                },\r\n                \"_cache_key\" : \"user_2_friends\"\r\n            }\r\n        }\r\n    }\r\n  }\r\n}'\r\n```\r\n\r\nThe above is higly optimized, both in a sense that the list of friends will not be fetched if the filter is already cached in the filter cache, and with internal LRU cache for fetching external values for the terms filter. Also, the entry in teh filter cache will not hold *all* the terms reducing the memory required for it.\r\n\r\n`_cache_key` is recommedned to be set, so its simple to clear the cache associated with it using the clear cache API. For example:\r\n\r\n```\r\ncurl -XPOST 'localhost:9200/tweets/_cache/clear?filter_keys=user_2_friends'\r\n```\r\n\r\nThe structure of the external terms document can also include array of inner objects, for example:\r\n\r\n```\r\ncurl -XPUT localhost:9200/users/user/2 -d '{\r\n   \"friends\" : [\r\n     {\r\n       \"id\" : \"1\"\r\n     },\r\n     {\r\n       \"id\" : \"2\"\r\n     }\r\n   ]\r\n}'\r\n```\r\n\r\nIn which case, the lookup path will be `friends.id`.\r\n\r\nThere is an additional cache involved, which caches the lookup of the lookup document to the actual terms. It is by default set to `10mb` LRU size, but can be explicitly set using `indices.cache.filter.terms.size`.\r\n\r\nAlso, consider using an index with a single shard and fully replicated across all nodes if the \"reference\" terms data is not large. The lookup terms filter will prefer to execute the get request on a local node if possible, reducing the need for networking.\r\n"},"comment":{"url":"https://api.github.com/repos/elastic/elasticsearch/issues/comments/96250712","html_url":"https://github.com/elastic/elasticsearch/issues/2674#issuecomment-96250712","issue_url":"https://api.github.com/repos/elastic/elasticsearch/issues/2674","id":96250712,"user":{"login":"brupm","id":336,"avatar_url":"https://avatars.githubusercontent.com/u/336?v=3","gravatar_id":"","url":"https://api.github.com/users/brupm","html_url":"https://github.com/brupm","followers_url":"https://api.github.com/users/brupm/followers","following_url":"https://api.github.com/users/brupm/following{/other_user}","gists_url":"https://api.github.com/users/brupm/gists{/gist_id}","starred_url":"https://api.github.com/users/brupm/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/brupm/subscriptions","organizations_url":"https://api.github.com/users/brupm/orgs","repos_url":"https://api.github.com/users/brupm/repos","events_url":"https://api.github.com/users/brupm/events{/privacy}","received_events_url":"https://api.github.com/users/brupm/received_events","type":"User","site_admin":false},"created_at":"2015-04-25T18:09:16Z","updated_at":"2015-04-25T18:09:16Z","body":"Is there an upper limit on who many terms filters I can have wrapped in a bool.should? "}}