# Compare 2 fields from separate doc "doc.field1 = doc2.field2"
**URL:** https://discuss.elastic.co/t/compare-2-fields-from-separate-doc-doc-field1-doc2-field2/39837
**Category:** Elasticsearch
**Created:** [January 22, 2016, 12:05am UTC](https://discuss.elastic.co/t/compare-2-fields-from-separate-doc-doc-field1-doc2-field2/39837 "2016-01-22T00:05:41Z")
**Posts on this page:** 12
**Page:** 1
### Author:  [@Anirban\_Chaudhuri](https://discuss.elastic.co/u/Anirban_Chaudhuri)
#### Post date: [January 22, 2016, 12:05am UTC](https://discuss.elastic.co/t/compare-2-fields-from-separate-doc-doc-field1-doc2-field2/39837/1 "2016-01-22T00:05:41Z")
Hi, Guess you'll can help...else please point time to the right thread.
I want to get the details where I have two documents as below:
"\_index": "mydata2\_ind1", "\_type": "mytype" and fields as "edt", "urid" , "location"
"\_index": "mydata\_ind1", "\_type": "mytype" and fields as "doj", "myid", "name"
I want to get the details where myid = urid.
I am not able to it with scripts, but there should a be simple way...
I am able to do it when I specify the value "105" as below , but it should be able to query with this condition myid = urid
/ **the below GET works fine** /
GET /my\*\_ind1/\_search
{
"query": {
"multi\_match": {
"query" : "105",
"fields": ["myid", "urid"]
}
}
}
/**this GET does not work and i know as this two separate doc, but is there a way? \*\*\*/
/**\*"No field found for [myid] in mapping with types []"\*\*_/
GET /my_\_ind1/\_search
{
"query": {
"filtered": {
"filter": {
"script": {
"script": "doc['myid'].value == doc['urid'].value"
}
}
}
}
}
all scripts are given below. ES version 2.1.1
/\*\*\* note i have tried with and widout doc\_values\*\*\*/
{
"dynamic": "true",
"properties": {
"myid": {"type": "integer","doc\_values" : true },
"name": {"type": "string", "index" : "not\_analyzed","doc\_values" : true},
"doj" : {"type": "date", "format": "MM/dd/yyyy HH:mm:ss||MM/dd/yyyy HH:mm","doc\_values" : true }
}
}
PUT /mydata\_ind1/mytype/105
{
"name" : "myname",
"myid" : 105,
"doj" : "10/20/2016 8:09"
}
{
"dynamic": "true",
"properties": {
"urid": {"type": "integer","doc\_values" : true},
"location": {"type": "string", "index" : "not\_analyzed","doc\_values" : true},
"edt" : {"type": "date", "format": "MM/dd/yyyy HH:mm:ss||MM/dd/yyyy HH:mm||yyyy-MM-dd HH:mm:ss" ,"doc\_values" : true }
}
}
PUT /mydata2\_ind1/mytype/105
{
"location" : "urname and myname",
"urid" : 105,
"edt" : "10/15/2016 20:09"
}
---
### Author:  [@warkolm](https://discuss.elastic.co/u/warkolm)
#### Post date: [January 22, 2016, 3:29am UTC](https://discuss.elastic.co/t/compare-2-fields-from-separate-doc-doc-field1-doc2-field2/39837/2 "2016-01-22T03:29:55Z")
Are these docs in different indices? It's not clear as your code is not properly formatted.
---
### Author:  [@Anirban\_Chaudhuri](https://discuss.elastic.co/u/Anirban_Chaudhuri)
#### Post date: [January 22, 2016, 4:19am UTC](https://discuss.elastic.co/t/compare-2-fields-from-separate-doc-doc-field1-doc2-field2/39837/3 "2016-01-22T04:19:55Z")
Yes, these are on different indexes.
PUT /mydata\_ind1/mytype/105
{
"name" : "myname",
"myid" : 105,
"doj" : "10/20/2016 8:09"
}
```
PUT /mydata2_ind1/mytype/105
{
"name" : "myname",
"myid" : 105,
"doj" : "10/15/2016 8:09"
}
```
---
### Author:  [@Anirban\_Chaudhuri](https://discuss.elastic.co/u/Anirban_Chaudhuri)
#### Post date: [January 22, 2016, 4:20am UTC](https://discuss.elastic.co/t/compare-2-fields-from-separate-doc-doc-field1-doc2-field2/39837/4 "2016-01-22T04:20:39Z")
and the other one
PUT /mydata2\_ind1/mytype/105
{
"location" : "urname and myname",
"urid" : 105,
"edt" : "10/15/2016 20:09"
}
---
### Author:  [@Anirban\_Chaudhuri](https://discuss.elastic.co/u/Anirban_Chaudhuri)
#### Post date: [January 22, 2016, 4:23am UTC](https://discuss.elastic.co/t/compare-2-fields-from-separate-doc-doc-field1-doc2-field2/39837/5 "2016-01-22T04:23:18Z")
Some more pointers to help you:
I validated it with sense as below, but still not sure what is missing...
```
GET /my*_ind1/_validate/query?explain
{
"query": {
"filtered": {
"filter": {
"script": {
"script": "doc['myid'].value == doc['urid'].value"
}
}
}
}
}
```
output:
```
{
"valid": true,
"_shards": {
"total": 2,
"successful": 2,
"failed": 0
},
"explanations": [
{
"index": "mydata2_ind1",
"valid": true,
"explanation": "+*:* #ScriptFilter([script: doc['myid'].value == doc['urid'].value, type: inline, lang: null, params: {}])"
},
{
"index": "mydata_ind1",
"valid": true,
"explanation": "+*:* #ScriptFilter([script: doc['myid'].value == doc['urid'].value, type: inline, lang: null, params: {}])"
}
]
}
```
---
### Author:  [@warkolm](https://discuss.elastic.co/u/warkolm)
#### Post date: [January 22, 2016, 4:49am UTC](https://discuss.elastic.co/t/compare-2-fields-from-separate-doc-doc-field1-doc2-field2/39837/6 "2016-01-22T04:49:00Z")
You can't do this.
---
### Author:  [@Anirban\_Chaudhuri](https://discuss.elastic.co/u/Anirban_Chaudhuri)
#### Post date: [January 22, 2016, 5:19am UTC](https://discuss.elastic.co/t/compare-2-fields-from-separate-doc-doc-field1-doc2-field2/39837/7 "2016-01-22T05:19:36Z")
but i can do it with multi\_match as below, the only thing is in this case i want to get the results where the values matches myid = urid as in real case i do not have the whole list of values.
```
GET /my*_ind1/_search
{
"query": {
"multi_match": {
"query" : "105",
"fields": ["myid", "urid"]
}
}
}
```
---
### Author:  [@Anirban\_Chaudhuri](https://discuss.elastic.co/u/Anirban_Chaudhuri)
#### Post date: [January 22, 2016, 7:54pm UTC](https://discuss.elastic.co/t/compare-2-fields-from-separate-doc-doc-field1-doc2-field2/39837/8 "2016-01-22T19:54:54Z")
Hello...is there someone can help me with this...
---
### Author:  [@Anirban\_Chaudhuri](https://discuss.elastic.co/u/Anirban_Chaudhuri)
#### Post date: [January 26, 2016, 9:08pm UTC](https://discuss.elastic.co/t/compare-2-fields-from-separate-doc-doc-field1-doc2-field2/39837/9 "2016-01-26T21:08:13Z")
For all those who want to know here is some more information:
[http://search.cpan.org/~drtech/Elastic-Model-0.52/lib/Elastic/Manual/NoSQL.pod](http://search.cpan.org/~drtech/Elastic-Model-0.52/lib/Elastic/Manual/NoSQL.pod)
But still can't figure out why the \*\_index and multi\_field was given... anyways...
---
### Author:  [@colings86](https://discuss.elastic.co/u/colings86)
#### Post date: [January 27, 2016, 8:59am UTC](https://discuss.elastic.co/t/compare-2-fields-from-separate-doc-doc-field1-doc2-field2/39837/10 "2016-01-27T08:59:24Z")
As @warkolm said above, this is not possible in ES. As the article you linked to explains, ES is not relational, you cannot perform joins, what you are asking for is effectively a join
---
### Author:  [@Anirban\_Chaudhuri](https://discuss.elastic.co/u/Anirban_Chaudhuri)
#### Post date: [January 29, 2016, 8:43pm UTC](https://discuss.elastic.co/t/compare-2-fields-from-separate-doc-doc-field1-doc2-field2/39837/11 "2016-01-29T20:43:14Z")
Hi, will you help to understand and correctly use ES...
```
PUT /my_ind/loc_type/100
{
"urid" : "100",
"location" : "denver",
"sdate" : "05/10/2016 08:09:00"
}
PUT /my_ind/name_type/100
{
"myid" : "100",
"name" : "Elasticsearch",
"sdate" : "05/10/2016 08:09:00"
}
GET /my_ind/_search
{
"query": {
"multi_match": {
"query" : "100",
"fields": ["myid", "urid"]
}
}
}
```
The above GET is success. All i want is to get the same result using myid == urid
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [July 5, 2017, 11:20pm UTC](https://discuss.elastic.co/t/compare-2-fields-from-separate-doc-doc-field1-doc2-field2/39837/12 "2017-07-05T23:20:17Z")