CODEWITHSUNDEEP
elasticsearch
J. Doverflow
J. Doverflow

Reputation: 51

Elasticsearch join-like query on multiple types and different fields

I have an Elasticsearch index called my_index which contains documents of two types, Type1 and Type2.

I've been trying to construct a join-like query which would return entities which match conditions on both document types, but I can't get it to work, and I also can't find any citation in the Elasticsearch multi-type or query documentation that says it's not possible.

The problem I'm trying to solve is avoiding having to manually join two result sets by getting all Type1 hits and all Type2 hits and doing the join outside of Elasticsearch, since the index has millions of documents.

The equivalent in SQL would be

select * from 
  Type1 inner join Type2 
    on Type2.EntityId = Type1.EntityId
where 
  Type1.Field = Condition AND
  Type2.Field = Condition [...]

The URL I'm using to query against is http://elastic/my_index/Type1,Type2/_search to include both document types.

If I perform a blank query against this URL, I get hits of both Type1 and Type2.

If I add a criterion for Type1, it works as expected:

{ "query": {
  "bool": {
    "must": [{
      "term": {
        "FieldOnType1": "lorem" } } ] } } }

Somehow Elasticsearch can infer that FieldOnType1 is indeed a field on Type1.

When I add a criterion for Type2, I don't get any hits:

{ "query": {
  "bool": {
    "must": [{
      "term": {
        "FieldOnType1": "lorem" } }, {
      "term": {
        "FieldOnType2": "ipsum" } } ] } } }

In reality, there are sometimes more than 2 term queries, or range queries and term queries.

I'm guessing the problem with the above query is that no single document can match both criteria at once.

I've tried

but everything gives me 0 hits.

Similar questions here suggest to use the Elasticsearch multi-search API instead of the search API, but that won't solve my "manual join" problem.

Is there a way to make an elaborate "OR" query that would allow queries on both types? Or something else?

Upvotes: 5

Views: 3497

Answers (1)

Egor
Egor

Reputation: 271

Try multi_match query (I use ES 6, so have index p/type):

GET index1,index2/_search
{
  "query":{
    "multi_match": {
      "query": "1",
      "fields": ["FieldOnType1", "FieldOnType2"]
    }
  }
}

If you need to use different fields, should should work:

GET test,test1/_search
{
   "query":{
     "bool": {
       "should": [
         {
           "term": {"firstName": "john"}
         },
         {
           "term": {"firstName1": "jerry1"}
         }
      ]
    }
   }
 }

Upvotes: 0

Related Questions