# Query where fields from different indexes are equal?

**URL:** <https://discuss.elastic.co/t/query-where-fields-from-different-indexes-are-equal/13528>\
**Category:** Elasticsearch\
**Created:** [September 9, 2013, 9:31pm UTC](https://discuss.elastic.co/t/query-where-fields-from-different-indexes-are-equal/13528 "2013-09-09T21:31:44Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Jamil\_Bou\_Kheir](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jamil_bou_kheir/32/1683_2.png) [@Jamil\_Bou\_Kheir](https://discuss.elastic.co/u/Jamil_Bou_Kheir)\
**Post date:** [September 9, 2013, 9:31pm UTC](https://discuss.elastic.co/t/query-where-fields-from-different-indexes-are-equal/13528/1 "2013-09-09T21:31:44Z")

</div>

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 ☹

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](mailto:elasticsearch+unsubscribe@googlegroups.com).  
For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out).

---

<div class="post-metadata">

**Author:** ![javanna](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/javanna/32/4698_2.png) [@javanna](https://discuss.elastic.co/u/javanna)\
**Post date:** [September 10, 2013, 4:05am UTC](https://discuss.elastic.co/t/query-where-fields-from-different-indexes-are-equal/13528/2 "2013-09-10T04:05:15Z")

</div>

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 🙂

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 blog[http://www.elasticsearch.org/blog/managing-relations-inside-elasticsearch/](http://www.elasticsearch.org/blog/managing-relations-inside-elasticsearch/)to know more on how you can manage relations in eleasticsearch. Also,  
there's a nice talk[http://www.elasticsearch.org/videos/bbuzz2013-document-relations-with-elasticsearch/](http://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 ☹
> 
> 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](mailto:elasticsearch+unsubscribe@googlegroups.com).  
For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out).

---

<div class="post-metadata">

**Author:** ![Jamil\_Bou\_Kheir](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jamil_bou_kheir/32/1683_2.png) [@Jamil\_Bou\_Kheir](https://discuss.elastic.co/u/Jamil_Bou_Kheir)\
**Post date:** [September 10, 2013, 4:34am UTC](https://discuss.elastic.co/t/query-where-fields-from-different-indexes-are-equal/13528/3 "2013-09-10T04:34:26Z")

</div>

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 🙂
> 
> 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 blog[http://www.elasticsearch.org/blog/managing-relations-inside-elasticsearch/](http://www.elasticsearch.org/blog/managing-relations-inside-elasticsearch/)to know more on how you can manage relations in eleasticsearch. Also,  
> there's a nice talk[http://www.elasticsearch.org/videos/bbuzz2013-document-relations-with-elasticsearch/](http://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 ☹
> > 
> > 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](mailto:elasticsearch+unsubscribe@googlegroups.com).  
For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out).

---

<div class="post-metadata">

**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:17am UTC](https://discuss.elastic.co/t/query-where-fields-from-different-indexes-are-equal/13528/4 "2017-07-06T02:17:22Z")

</div>