Join elasticsearch indices while matching fields in nested/inner objects

What you're trying to do worked for me, unless I'm missing something. What version of Elasticsearch are you using? I'm using 1.3.4.

So I created both indices and added the docs you have listed:

curl -XPUT "http://localhost:9200/users"curl -XPUT "http://localhost:9200/users/user/2 " -d '{   "followers" : [  {    "userId":"1",    "username":"abc",    "location":"xyz"   },   {    "userId":"3",    "username":"def",    "location":"xyz"   }   ]}'curl -XPUT "http://localhost:9200/tweets"curl -XPUT "http://localhost:9200/tweets/tweet/1 " -d'{   "user" : "2"}'curl -XPUT "http://localhost:9200/tweets/tweet/2 " -d'{   "user" : "1"}'

then ran your search query:

curl -XPOST "http://localhost:9200/tweets/_search " -d'{   "query": {      "filtered": {         "filter": {            "terms": {               "user": {                  "index": "users",                  "type": "user",                  "id": "2",                  "path": "followers.userId"               },               "_cache_key": "user_2_friends"            }         }      }   }}'

and got back this result:

{   "took": 2,   "timed_out": false,   "_shards": {      "total": 5,      "successful": 5,      "failed": 0   },   "hits": {      "total": 1,      "max_score": 1,      "hits": [         {            "_index": "tweets",            "_type": "tweet",            "_id": "2",            "_score": 1,            "_source": {               "user": "1"            }         }      ]   }}

Here is the code I used:

http://sense.qbox.io/gist/4a2a2d77d0b6f4502ff6c5022b268acfa65ee6d2

Clear the indices if you have any

curl -XDELETE "http://example.com:9200/currencylookup/"curl -XDELETE "http://example.com:9200/currency/"

Create the lookup table

curl -XPUT http://example.com:9200/currencylookup/type/2 -d '{ "conv" : [ {  "currency":"usd","username":"abc", "location":"USA" }, {  "currency":"inr", "username":"def", "location":"India" },{  "currency":"IDR", "username":"def", "location":"Indonesia" }]}'

Lets put some dummy docs

curl -XPUT "http://example.com:9200/currency/type/USA" -d '{ "amount":"100", "currency":"usd", "location":"USA" }'curl -XPUT "http://example.com:9200/currency/type/JPY" -d '{ "amount":"50", "currency":"JPY", "location":"JAPAN" }'curl -XPUT "http://example.com:9200/currency/type/INR" -d '{ "amount":"50", "currency":"inr", "location":"INDIA" }'curl -XPUT "http://example.com:9200/currency/type/IDR" -d '{ "amount":"30", "currency" : "IDR", "location": "Indonesia" }'

Time to check the output

curl http://example.com:9200/currency/_search?pretty -d '{   "query" : { "filtered" : {   "filter" : {     "terms" : {       "currency" : {         "index" : "currencylookup",         "type" : "type",         "id" : "2",         "path" : "conv.currency"       },       "_cache_key" : "currencyexchange"     }   } }   } }'

Results

# curl http://example.com:9200/currency/_search?pretty -d '{   "query" : { "filtered" : {   "filter" : {     "terms" : {       "currency" : {         "index" : "currencylookup",         "type" : "type",         "id" : "2",         "path" : "conv.currency"       },       "_cache_key" : "currencyexchange"     }   } }   } }'{  "took" : 2,  "timed_out" : false,  "_shards" : {    "total" : 5,    "successful" : 5,    "failed" : 0  },  "hits" : {    "total" : 2,    "max_score" : 1.0,    "hits" : [ {      "_index" : "currency",      "_type" : "type",      "_id" : "INR",      "_score" : 1.0,      "_source":{ "amount":"50", "currency":"inr", "location":"INDIA" }    }, {      "_index" : "currency",      "_type" : "type",      "_id" : "USA",      "_score" : 1.0,      "_source":{ "amount":"100", "currency":"usd", "location":"USA" }    } ]  }}

Conclusion

Capital letters are culprit here.

You can see 'IDR' is in caps so the match is failed for it and 'JPY' is not in look up even if it was there it would not have got matched because it is in caps.

cross matching values must be in small letters or numbers like

eg:

• abc
• 1abc