Hierarchical query with terms lookup filter

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/
{
"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 :wink:

--
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.

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 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/
{
"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 :wink:

--
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.

Thank for your reply Ivan,

curl -XPOST 'http://localhost:9200/organisations' -d '{
"index": {
"number_of_shards": 1
}
}'

Create mapping organisations

curl -X PUT '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' -d
'{"title":"Organisation A","duplicated_pk":"1","parent_ids":[1]}'
curl -X POST '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' -d
'{"title":"Organisation C","duplicated_pk":"3","parent_ids":[1,2,3]}'
curl -X POST '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'

// working examples when trying to get all parent of organisation with _id 4
curl -X POST '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/' -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<javascript:>

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/
{
"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 :wink:

--
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 <javascript:>.
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.

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'

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