Query where fields from different indexes are not all equal?

Elastic Stack Elasticsearch
1

Hi,

Can i query across 2 indexes with just 2 common fields and get other fields in the results ?
example :
Index 1 has fields : A, B, C, D
Index 2 has fields : A,B,E,F,G

Only A and B are common between these indexes, however i want all the fields in the response.
example my result should contain : A,B,C,D,E,F,G

Currently when i run the query, i only get A,B and don't see the rest of the fields.

Let me know how this is possible.

2

Hi Sam,

A couple of quick examples, you can test these using Dev Console in Kibana if you want.

# index two docs into two indices - index1/index2
# a field in one doc in each index has a different value to the rest of the fields in that doc, to demonstrate a search example
POST /index1/doc
{
  "A": "testing1 sample",
  "B": "testing2 sample",
  "C": "testing3 sample",
  "D": "testing4 sample"
}

POST /index1/doc
{
  "A": "testing1 sample",
  "B": "testing2 sample",
  "C": "testing3 sample",
  "D": "testing4 only D has this"
}

POST /index2/doc
{
  "A": "testing1 sample",
  "B": "testing2 sample",
  "E": "testing3 sample",
  "F": "testing4 sample",
  "G": "testing5 sample"
}

POST /index2/doc
{
  "A": "testing1 sample",
  "B": "testing2 sample",
  "E": "testing3 sample",
  "F": "testing4 sample",
  "G": "testing5 only G has this"
}

# search both indices for any docs with any fields containing the word "sample"
GET /index1,index2/_search
{
  "query": {
    "match": {
      "_all": "sample"
    }
  }
}

# search both indices for any docs with any field containing the word "only"
GET /index1,index2/_search
{
  "query": {
    "match": {
      "_all": "only"
    }
  }
}

#multi_match query allows you to list the fields you might want to search
GET /index1,index2/_search
{
  "query": {
    "multi_match" : {
      "query":  "sample",
      "fields": [ "A", "B", "C", "D", "E", "F", "G" ] 
    }
  }
}

# search for "only" with multi_match
GET /index1,index2/_search
{
  "query": {
    "multi_match" : {
      "query":  "only",
      "fields": [ "A", "B", "C", "D", "E", "F", "G" ] 
    }
  }
}

# use the _all field with multi_match, note that _all will be deprecated and off by default in 6.0 - https://www.elastic.co/guide/en/elasticsearch/reference/master/mapping-all-field.html
GET /index1,index2/_search
{
  "query": {
    "multi_match" : {
      "query":  "only",
      "fields": [ "_all" ] 
    }
  }
}

Not sure if you meant your issue was with searching or with the results returned.

Can you provide an example doc with an example search?

1 Like
4

Thanks a lot geekpete, but how to filter on multi_match? i am trying below Query and it is failing.

{
  "query": {
    "bool": {
      "must": {
        "multi_match": {
          "query": "iPhone",
          "type": "most_fields",
          "fields": [
            "comment",
            "content.main.title"
          ]
        }
      },
      "filter": {
        "term": {
          "locale": "en_US"
        }
      }
    }
  }
}
5

How is it failing? It's just not returning the expected results?

The way you've used the bool filter with the bool must multi_match appears correct.
Can you test only your filter to ensure that's working as expected.
Then test only your multi_match query to ensure that's working as expected.

So testing just the filter:

{
  "query": {
    "bool": {
      "filter": {
        "term": {
          "locale": "en_US"
        }
      }
    }
  }
}

Testing just the multi_match:

{
  "query": {
    "bool": {
      "must": {
        "multi_match": {
          "query": "iPhone",
          "type": "most_fields",
          "fields": [
            "comment",
            "content.main.title"
          ]
        }
      }
    }
  }
}

And do you see expected results from those separate queries?

So using the example docs I had above, the same type of queries with those doc would be:

# test multi_match for "testing3" in a set of fields
GET /index1,index2/_search
{
  "query": {
    "bool": {
      "must": {
        "multi_match": {
          "query": "testing3",
          "fields": [
            "A",
            "B",
            "C",
            "D",
            "E",
            "F",
            "G"
          ]
        }
      }
    }
  }
}

# Now test the filter by itself, only docs with "only" in a "G" field
GET /index1,index2/_search
{
  "query": {
    "bool": {
      "filter": {
        "term": {
          "G": "only"
        }
      }
    }
  }
}

# now combine the known working multi_match query with the working filter
GET /index1,index2/_search
{
  "query": {
    "bool": {
      "must": {
        "multi_match": {
          "query": "testing3",
          "fields": [
            "A",
            "B",
            "C",
            "D",
            "E",
            "F",
            "G"
          ]
        }
      },
      "filter": {
        "term": {
          "G": "only"
        }
      }
    }
  }
}
1 Like
6 
{
  "query": {
    "bool": {
      "filter": {
        "term": {
          "locale": "en_US"
        }
      }
    }
  }
}

The Above Query is not working for some reason, but multi_match is working fine.

7

It is working fine now, looks like locale:"en_US" is case sensitive, after giving locale:"en_us" it is working fine.
Appreciate you help thanks a lot.

1 Like
8

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.