Join elasticsearch indices while matching fields in nested/inner objects

Question

I am trying to join 2 elasticsearch indices by using terms filter lookup. I referred to http://www.elasticsearch.org/blog/terms-filter-lookup/ and http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/query-dsl-terms-filter.html. These Examples lookup on an array of fields like "followers" : ["1", "3"] and join works fine for similar data.

My requirement is to join with a field inside an array of objects. When I extend the above example to include an array of objects, my query fails. Following is the sample data:

PUT /users/user/2 {
   "followers" : [
  {
    "userId":"1",
    "username":"abc",
    "location":"xyz"
   },
   {
    "userId":"3",
    "username":"def",
    "location":"xyz"
   }
}
]
}

PUT /tweets/tweet/1 {
   "user" : "2"
}

PUT /tweets/tweet/2 {
   "user" : "1"
}

I am now trying to find tweets that are created by followers of user 2

POST /tweets/_search {
  "query" : {
"filtered" : {
  "filter" : {
    "terms" : {
      "user" : {
        "index" : "users",
        "type" : "user",
        "id" : "2",
        "path" : "followers.userId"
      },
      "_cache_key" : "user_2_friends"
    }
  }
}
  }
}

My search results are 0 for above query. I tried 2 other approaches as well 1)declare the followers object as a nested object during mapping and use "nested" in the query, 2)tried to add a match query for followers.userId after giving path as "followers". None yielded results.

Does terms filter lookup support array of objects? Any pointers to solving my problem would be of great help

Sloan Ahrens · Accepted Answer · 2014-12-17 18:42:45Z

1

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

answered Dec 17, 2014 at 18:42

Sloan Ahrens

8,7382 gold badges32 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

adkompella Over a year ago

I was actually trying with another dataset. But since I did not want to put that data here, I modified the tweets example and posted. I am using es140b, but you are right same code worked for me when I tried it now. But my original dataset is still not getting correlated.

curl -XPUT "http://localhost:9200/ctest/cur/1" -d ' { 	"conv":[ 	{     "currency":"USD", 	"name": "US Dollar" 	}, {     "currency":"INR", 	"name": "Indian Rupee" }, {     "currency":"IDR", 	"name": "Indonesian Rupiah" } ] }'

adkompella Over a year ago

curl -XPUT "http://localhost:9200/etest/e/1" -d '{ "amount":"100", "currency":"USD", "location":"USA" }'  curl -XPUT "http://localhost:9200/etest/e/2" -d '{ "amount":"50", "currency":"JPY", "location":"JAPAN" }'

and no results for my query

curl -XPOST "http://localhost:9200/etest/e/_search" -d '{   "query" : {     "filtered" : {       "filter" : {         "terms" : {           "currency" : {             "index" : "ctest",             "type" : "cur",             "id" : "1",             "path" : "conv.currency"           },           "_cache_key" : "test"         }       }     }   } }'

user2486495 · Accepted Answer · 2014-12-19 05:12:55Z

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

case sensitivity seems to be the problem, it now works for me. However there is one small correction. Looks like the matching works fine even if there is upper case in the currency documents as long as the lookup documents have lower case.
There is an option to solve this issue, Please check stackoverflow.com/questions/24811243/…

Collectives™ on Stack Overflow

Join elasticsearch indices while matching fields in nested/inner objects

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related