CODEWITHSUNDEEP
elasticsearchkibanaelastic-stackelk
GTpy
GTpy

Reputation: 13

Search Multiple Fields in Kibana /ElasticSearch

In SQL i have 

select Column1 , column2, column3 from Table where Column4 in ['a','b','c','d']

I'm trying to achieve the SQL statement in Kibana, I face challenge in writing the In condition. 

{
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "field1": "X"
          }
        },
        {
          "term": {
            "field2": "Z"
          }
        }
      ],

    }
  }
}

Upvotes: 1

Views: 4229

Answers (3)

user11935734
user11935734

Reputation:

For getting above result, you can either use bool query must clause or should clause with minimum_should_match parameter. You can refer more about this from here.

Mapping created for your above columns is :

Mapping

"mappings" : {
        "properties" : {
            "column1" : {
                "type":"text"
            },
            "column2" : {
                "type":"text"
            },
            "column3" : {
                "type":"text"
            }, 
            "column4" : {
                "type":"text"
            }
        }
    }

You can get your search results by two ways :

  1. You can pass all your search values in must clause with match query. Match by default uses OR operator . So, it will match any document whose column4 values matches any of these "a b c d". You can read about default operator of match query from here.

Query :

    {
    "_source": [
        "column1",
        "column2",
        "column3"
    ],
    "query": {
        "match": {
            "column4": "a b c d"
        }
    }
}
  1. You can also you should clause with match query for every values. And restrict to only one match by using minimum_should_match parameter.

Query 2:

 {
     "_source": ["column1", "column2", "column3"],
    "query" : {
        "bool" : {
            "should" : [
                {
                    "match" : {
                        "column4" : "a"
                    }
                },
                {
                    "match" : {
                        "column4" : "b"
                    }
                },
                {
                    "match" : {
                        "column4" : "c"
                    }
                },
                {
                    "match" : {
                        "column4" : "d"
                    }
                }
            ],
            "minimum_should_match" : "1"
        }
    }
}

For the type of data you have for column4 you can go with first query.

Upvotes: 2

Kumar V
Kumar V

Reputation: 1650

We can use "query_string" query also to achieve the same. Since we are looking to match multiple values on the same field, query_string can be used with default_field set to the search field. 

{
   "_source": ["column1", "column2", "column3"],
   "query": {
        "query_string" : {
            "query" : "a OR b OR c OR d",
            "default_field" : "column4"
        }
    }
}

Upvotes: 0

Ori Cohen
Ori Cohen

Reputation: 185

Simply write the value you want as an array.

In your example, write [a,b,c,d] instead of "X"

Upvotes: 0

Related Questions