Elasticsearch join-like query on multiple types and different fields

Question

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

The two document types contain different data about the same type of entity.
The two document types both contain the ID of the related entity.

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

using should instead of must, and I've tried
qualifying the field names with type names, and I've tried
many variations of the query (including using filters instead of queries)

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?

bool query with should should work for you. Please post exact query you tried. Also, check that you get results when you run query for type2 only — Egor
– Egor, Commented Dec 6, 2017 at 9:40

Egor · Accepted Answer · 2017-12-06 09:38:18Z

0

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

edited Dec 6, 2017 at 9:38

answered Dec 5, 2017 at 13:38

Egor

2712 silver badges6 bronze badges

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

1 Comment

J. Doverflow Over a year ago

I'm not searching for the same value on both fields. I'm looking for documents where FieldOnType1=lorem and FieldOnType2=ipsum for example. I've updated the question to be more clear about this.

Collectives™ on Stack Overflow

Elasticsearch join-like query on multiple types and different fields

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related