Use of Filter on multiple indices

Elastic Stack Elasticsearch
1

Hi,

I try to query two different indices with a filter on a specific field.

Lets say I have an index1 and an index2 where both of the indices have a field named name and just index2 have an extra field code.

Lets say I put 3 documents:

PUT index1/index1/1
{
    "name" : "kimchy"
}
PUT index2/index2/1
 {
     "name" : "kimchy",
     "code" : "secret"
 }
PUT index2/index2/2
 {
     "name" : "kimchy",
     "code" : "notSecret"
 }

I would like to find all documents from index1 where name = kimchy and from index2 where name = kimchy and code = secret.

Means document index1/1 and index2/1 should be in the result.

I tried to use filter to filter the code field. But unfortunately I just get document index2/2 because index1 does not have code field and does not match.

GET index1,index2/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "name": "kimchy"
          }
        }
      ],
      "filter": {
        "term": {
          "code": "secret"
        }
      }
    }
  }
}

Is there a way to achive that?

If you need any additional info, please let me know.

Thanks!

2

I think I found a solution by using should and must_not:

GET index1,index2/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "name": "fendels"
          }
        }
      ],
      "filter": {
        "bool": {
          "should": [
            {
              "term": {
                "code": "secret"
              }
            },
            {
              "bool": {
                "must_not": {
                  "exists": {
                    "field": "code"
                  }
                }
              }
            }
          ]
        }
      }
    }
  }
}
3

I think your solution does not exactly answer what you described.

I would like to find all documents from index1 where name = kimchy and from index2 where name = kimchy and code = secret.

Here is my proposal:

GET index1,index2/_search
{
  "query": {
    "bool": {
      "should": [
        {
          "bool": {
            "must": [
              {
                "match": {
                  "name": "kimchy"
                }
              }
            ], 
            "filter": [
              {
                "term": {
                  "_index": "index1"
                }
              }
            ]
          }
        },
        {
          "bool": {
            "must": [
              {
                "match": {
                  "name": "kimchy"
                }
              }
            ], 
            "filter": [
              {
                "term": {
                  "_index": "index2"
                }
              },
              {
                "term": {
                  "code": "secret"
                }
              }
            ]
          }
        }
      ]
    }
  }
}
4

Thanks for your proposal. The result is the same but the query is much better structured and describes also my question.

I will go further with your solution.

5

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