# Hierarchical query with terms lookup filter **URL:** \ **Category:** Elasticsearch\ **Created:** [May 8, 2013, 4:32pm UTC](https://discuss.elastic.co/t/hierarchical-query-with-terms-lookup-filter/11879 "2013-05-08T16:32:19Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![Mathieu\_Roy](https://avatars.discourse-cdn.com/v4/letter/m/9de0a6/32.png) [@Mathieu\_Roy](https://discuss.elastic.co/u/Mathieu_Roy)\ **Post date:** [May 8, 2013, 4:32pm UTC](https://discuss.elastic.co/t/hierarchical-query-with-terms-lookup-filter/11879/1 "2013-05-08T16:32:19Z")
Hi there, This is my first post in this group, and i'am new to elasticsearch, i would like to thank everyone who contribute in the developpement of elasticsearch, this is really an outstanding product. Now, here is my problem: I am trying to build a closure table like schema with the help of the terms filter. I have an Index and a Type called "organisations" and "organisation" respectively. Organisations can be nested and i will have a lots of organisations, so nested mapping is not an option, i could use parent child relationship but this would require recursive query to get parents or childs which i would like to avoid. Here is an example of the query i am trying to achieve: [http://localhost:9200/organisations/\_search/](http://localhost:9200/organisations/_search/) { "query": { "filtered": { "filter": { "and": [ { "terms": { "\_id": { "index": "organisations", "type": "organisation", "id": "2", "path": "parent\_ids" } } } ] } } } } The parent\_ids field contain all the parents ids as an array. The above query work great if i duplicate the \_id field and and use this new field "duplicate\_id" instead of \_id in the terms filter. But duplicating the \_id field require after insert of a record to make an update to this same record, this is not so bad, but i would like to avoid it if possible. Also, it would be even easier to use the \_id field straight away in the terms filter, is there a reason i cannot use this field in the terms filter? Thank you very much P.S: sorry for my bad english πŸ˜‰ -- 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:** ![Ivan](https://avatars.discourse-cdn.com/v4/letter/i/df788c/32.png) [@Ivan](https://discuss.elastic.co/u/Ivan)\ **Post date:** [May 9, 2013, 4:23am UTC](https://discuss.elastic.co/t/hierarchical-query-with-terms-lookup-filter/11879/2 "2013-05-09T04:23:14Z")
Can you provide a few sample documents? I would help me, and I assume others, wrap my mind around the issue. -- Ivan On Wed, May 8, 2013 at 9:32 AM, Mathieu Roy [mr.math.roy@gmail.com](mailto:mr.math.roy@gmail.com) wrote: > Hi there, > > This is my first post in this group, and i'am new to elasticsearch, i > would like to thank everyone who contribute in the developpement of > elasticsearch, this is really an outstanding product. > > Now, here is my problem: > I am trying to build a closure table like schema with the help of the > terms filter. > > I have an Index and a Type called "organisations" and "organisation" > respectively. > Organisations can be nested and i will have a lots of organisations, so > nested mapping is not an option, i could use parent child relationship but > this would require recursive query to get parents or childs which i would > like to avoid. > > Here is an example of the query i am trying to achieve: > > [http://localhost:9200/organisations/\_search/](http://localhost:9200/organisations/_search/) > { > "query": { > "filtered": { > "filter": { > "and": [ > { > "terms": { > "\_id": { > "index": "organisations", > "type": "organisation", > "id": "2", > "path": "parent\_ids" > } > } > } > ] > } > } > } > } > > The parent\_ids field contain all the parents ids as an array. > The above query work great if i duplicate the \_id field and and use this > new field "duplicate\_id" instead of \_id in the terms filter. > > But duplicating the \_id field require after insert of a record to make an > update to this same record, this is not so bad, but i would like to avoid > it if possible. > > Also, it would be even easier to use the \_id field straight away in the > terms filter, is there a reason i cannot use this field in the terms filter? > > Thank you very much > > P.S: sorry for my bad english πŸ˜‰ > > -- > 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:** ![Mathieu\_Roy](https://avatars.discourse-cdn.com/v4/letter/m/9de0a6/32.png) [@Mathieu\_Roy](https://discuss.elastic.co/u/Mathieu_Roy)\ **Post date:** [May 9, 2013, 5:12pm UTC](https://discuss.elastic.co/t/hierarchical-query-with-terms-lookup-filter/11879/3 "2013-05-09T17:12:40Z")
Thank for your reply Ivan, curl -XPOST '[http://localhost:9200/organisations](http://localhost:9200/organisations)' -d '{ "index": { "number\_of\_shards": 1 } }' # Create mapping organisations curl -X PUT '[http://localhost:9200/organisations/organisation/\_mapping](http://localhost:9200/organisations/organisation/_mapping)' -d ' { "properties" : { "title" : { "type" : "string" }, // We need to duplicate the \_id field because we cannot use it in the terms filter, maybe there is an other way? "duplicated\_pk":{ "type" : "integer" }, "parent\_ids":{ "type" : "integer" } } }' curl -X POST '[http://localhost:9200/organisations/organisation/1](http://localhost:9200/organisations/organisation/1)' -d '{"title":"Organisation A","duplicated\_pk":"1","parent\_ids":[1]}' curl -X POST '[http://localhost:9200/organisations/organisation/2](http://localhost:9200/organisations/organisation/2)' -d '{"title":"Organisation B","duplicated\_pk":"2","parent\_ids":[1,2]}' curl -X POST '[http://localhost:9200/organisations/organisation/3](http://localhost:9200/organisations/organisation/3)' -d '{"title":"Organisation C","duplicated\_pk":"3","parent\_ids":[1,2,3]}' curl -X POST '[http://localhost:9200/organisations/organisation/4](http://localhost:9200/organisations/organisation/4)' -d '{"title":"Organisation D","duplicated\_pk":"4","parent\_ids":[1,2,3,4]}' curl -X POST '[http://localhost:9200/organisations/\_refresh](http://localhost:9200/organisations/_refresh)' // working examples when trying to get all parent of organisation with \_id 4 curl -X POST '[http://localhost:9200/organisations/\_search/](http://localhost:9200/organisations/_search/)' -d '{ "query": { "filtered": { "filter": { "terms": { "duplicated\_pk": { "index": "organisations", "type": "organisation", "id": "4", "path": "parent\_ids" } } } } } }' // Non working examples when trying to get all parent of organisation with \_id 4 // Why looking up by \_id field doesn't work? curl -X POST '[http://localhost:9200/organisations/\_search/](http://localhost:9200/organisations/_search/)' -d '{ "query": { "filtered": { "filter": { "terms": { // Replaced field name "duplicated\_pk" by "\_id" "\_id": { "index": "organisations", "type": "organisation", "id": "4", "path": "parent\_ids" } } } } } }' In fact the question is really simple, but I complicated things a bit. As you can see above code lookup by "\_id" field doesn't work but lookup by "duplicated\_pk" work, is this a Lucene limitation or an elasticsearch one, or simply a bug? Thank you Le jeudi 9 mai 2013 00:23:14 UTC-4, Ivan Brusic a Γ©crit : > Can you provide a few sample documents? I would help me, and I assume > others, wrap my mind around the issue. > > -- > Ivan > > On Wed, May 8, 2013 at 9:32 AM, Mathieu Roy \<[mr.ma...@gmail.com](mailto:mr.ma...@gmail.com)\ > > > wrote: > > > Hi there, > > > > This is my first post in this group, and i'am new to elasticsearch, i > > would like to thank everyone who contribute in the developpement of > > elasticsearch, this is really an outstanding product. > > > > Now, here is my problem: > > I am trying to build a closure table like schema with the help of the > > terms filter. > > > > I have an Index and a Type called "organisations" and "organisation" > > respectively. > > Organisations can be nested and i will have a lots of organisations, so > > nested mapping is not an option, i could use parent child relationship but > > this would require recursive query to get parents or childs which i would > > like to avoid. > > > > Here is an example of the query i am trying to achieve: > > > > [http://localhost:9200/organisations/\_search/](http://localhost:9200/organisations/_search/) > > { > > "query": { > > "filtered": { > > "filter": { > > "and": [ > > { > > "terms": { > > "\_id": { > > "index": "organisations", > > "type": "organisation", > > "id": "2", > > "path": "parent\_ids" > > } > > } > > } > > ] > > } > > } > > } > > } > > > > The parent\_ids field contain all the parents ids as an array. > > The above query work great if i duplicate the \_id field and and use this > > new field "duplicate\_id" instead of \_id in the terms filter. > > > > But duplicating the \_id field require after insert of a record to make an > > update to this same record, this is not so bad, but i would like to avoid > > it if possible. > > > > Also, it would be even easier to use the \_id field straight away in the > > terms filter, is there a reason i cannot use this field in the terms filter? > > > > Thank you very much > > > > P.S: sorry for my bad english πŸ˜‰ > > > > -- > > 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 [elasticsearc...@googlegroups.com](mailto:elasticsearc...@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:** ![brian\_yoder](https://avatars.discourse-cdn.com/v4/letter/b/f1d935/32.png) [@brian\_yoder](https://discuss.elastic.co/u/brian_yoder)\ **Post date:** [May 9, 2013, 8:02pm UTC](https://discuss.elastic.co/t/hierarchical-query-with-terms-lookup-filter/11879/4 "2013-05-09T20:02:03Z")
I don't believe \_id is a field; it's a field within the meta-data (more like a document's attribute than a field). You'll need to issue a GET by index, type, and id. Something more along the lines of: curl -XGET '[http://localhost:9200/orgainisations/organisation/4](http://localhost:9200/orgainisations/organisation/4)' On Thursday, May 9, 2013 1:12:40 PM UTC-4, Mathieu Roy wrote: > As you can see above code lookup by "\_id" field doesn't work but lookup by > "duplicated\_pk" work, is this a Lucene limitation or an elasticsearch one, > or simply a bug? -- 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://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 6, 2017, 2:37am UTC](https://discuss.elastic.co/t/hierarchical-query-with-terms-lookup-filter/11879/5 "2017-07-06T02:37:17Z")