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


(Anirban Chaudhuri) #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"
}


(Mark Walkom) #2

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


(Anirban Chaudhuri) #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"
}

(Anirban Chaudhuri) #4

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


(Anirban Chaudhuri) #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: {}])"
    }
  ]
}

(Mark Walkom) #6

You can't do this.


(Anirban Chaudhuri) #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"]
     }
  }
}

(Anirban Chaudhuri) #8

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


(Anirban Chaudhuri) #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...


(Colin Goodheart-Smithe) #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


(Anirban Chaudhuri) #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


(system) #12