Compare 2 fields from separate doc "doc.field1 = doc2.field2"

Elastic Stack Elasticsearch
1

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"
}

2

Are these docs in different indices? It's not clear as your code is not properly formatted.

3

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"
}
4

and the other one
PUT /mydata2_ind1/mytype/105
{
"location" : "urname and myname",
"urid" : 105,
"edt" : "10/15/2016 20:09"
}

5

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: {}])"
    }
  ]
}
6

You can't do this.

7

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"]
     }
  }
}
8

Hello...is there someone can help me with this...

9

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

But still can't figure out why the *_index and multi_field was given... anyways...

10

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

11

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