Query where fields from different indexes are equal?


(Jamil Bou Kheir) #1

Hi all,

I'm trying to build a query that will return all documents where field1
from index1 equals field2 from index2. A query_string representation of
this might be "human.first_name := animal.nick_name". I've read up on
script filters but can't seem to get them to work -- all I can manage is a
bunch of exceptions :frowning:

For example, suppose my document structure for a "people" index resembles
the following:

{
_source: {
@fields: {
human: {
first_name: String
}
}
}
}

And my document structure for an "animals" index resembles the following:

{
_source: {
@fields: {
animal: {
nick_name: String
}
}
}
}

I then tried using a script to find all documents where a human's
first_name matches an animal's nick_name:

{
"query": {
"filtered": {
"filter": {
"script": {
"script": "doc['human.first_name'].value ==
doc['animal.nick_name'].value"
}
}
}
}
}

But this just gives me a "Error: could not access: human" exception. Anyone
know how I may be able to perform this?

Thanks!

Jamil

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


(Luca Cavanna) #2

Hi,
the problem here is that scripts are supposed to be executed on a single
document. That's what the doc variable is in a script: the current
document. You can't compare different documents from a script. Also, it
seems like you are trying to do some kind of cross-index join, which is not
the best thing to do with elasticsearch :slight_smile:

Elasticsearch does help with document relations with nested documents and
parent child, or you can also denormalize your data so that a join is not a
join anymore, which is something that you would usually do with a NoSQL
database too. Have a look at this bloghttp://www.elasticsearch.org/blog/managing-relations-inside-elasticsearch/to know more on how you can manage relations in eleasticsearch. Also,
there's a nice talkhttp://www.elasticsearch.org/videos/bbuzz2013-document-relations-with-elasticsearch/available, which was given at last Berlin Buzzwords conference.

Hope this helps
Luca

On Monday, September 9, 2013 11:31:44 PM UTC+2, Jamil Bou Kheir wrote:

Hi all,

I'm trying to build a query that will return all documents where field1
from index1 equals field2 from index2. A query_string representation of
this might be "human.first_name := animal.nick_name". I've read up on
script filters but can't seem to get them to work -- all I can manage is a
bunch of exceptions :frowning:

For example, suppose my document structure for a "people" index resembles
the following:

{
_source: {
@fields: {
human: {
first_name: String
}
}
}
}

And my document structure for an "animals" index resembles the following:

{
_source: {
@fields: {
animal: {
nick_name: String
}
}
}
}

I then tried using a script to find all documents where a human's
first_name matches an animal's nick_name:

{
"query": {
"filtered": {
"filter": {
"script": {
"script": "doc['human.first_name'].value ==
doc['animal.nick_name'].value"
}
}
}
}
}

But this just gives me a "Error: could not access: human" exception.
Anyone know how I may be able to perform this?

Thanks!

Jamil

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


(Jamil Bou Kheir) #3

Hey Luca,

Thanks for your response. I was afraid of that :-(.

The problem is that the user will be specifying the fields to correlate, so
the documents need to be "joined" at query time based on the specified
field to match on... e.g. many-to-many relationship. Probably a job more
suited to a relational database, but perhaps ES will offer some basic
cross-document correlation / comparison features in the near future. I'm
more than willing to take the performance hit :-).

For now, I'll have to script my app to allow the user to perform recursive
queries.

Jamil

On Monday, September 9, 2013 9:05:15 PM UTC-7, Luca Cavanna wrote:

Hi,
the problem here is that scripts are supposed to be executed on a single
document. That's what the doc variable is in a script: the current
document. You can't compare different documents from a script. Also, it
seems like you are trying to do some kind of cross-index join, which is not
the best thing to do with elasticsearch :slight_smile:

Elasticsearch does help with document relations with nested documents and
parent child, or you can also denormalize your data so that a join is not a
join anymore, which is something that you would usually do with a NoSQL
database too. Have a look at this bloghttp://www.elasticsearch.org/blog/managing-relations-inside-elasticsearch/to know more on how you can manage relations in eleasticsearch. Also,
there's a nice talkhttp://www.elasticsearch.org/videos/bbuzz2013-document-relations-with-elasticsearch/available, which was given at last Berlin Buzzwords conference.

Hope this helps
Luca

On Monday, September 9, 2013 11:31:44 PM UTC+2, Jamil Bou Kheir wrote:

Hi all,

I'm trying to build a query that will return all documents where field1
from index1 equals field2 from index2. A query_string representation of
this might be "human.first_name := animal.nick_name". I've read up on
script filters but can't seem to get them to work -- all I can manage is a
bunch of exceptions :frowning:

For example, suppose my document structure for a "people" index resembles
the following:

{
_source: {
@fields: {
human: {
first_name: String
}
}
}
}

And my document structure for an "animals" index resembles the following:

{
_source: {
@fields: {
animal: {
nick_name: String
}
}
}
}

I then tried using a script to find all documents where a human's
first_name matches an animal's nick_name:

{
"query": {
"filtered": {
"filter": {
"script": {
"script": "doc['human.first_name'].value ==
doc['animal.nick_name'].value"
}
}
}
}
}

But this just gives me a "Error: could not access: human" exception.
Anyone know how I may be able to perform this?

Thanks!

Jamil

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


(system) #4