How to Query with Multiple Criteria in Elasticsearch

Introduction

In the real world, we often query data for more than one criteria. For instance, say within a user index you want to query males—that’ s one criteria. In addition, you want to query those males older than age 35—that’s the second criteria. What’s more, you’ll likely want to continue to further narrow your target data search to even more specific criteria. Well, you can query multiple criteria within Elasticsearch. This tutorial shows you step-by-step how it’s done. If you’re already familiar with multiple criteria querying, click here to go directly to Just the Code.

Add multiple criteria by using the bool data type

Use the bool data type to combine two or more criteria. This way, you’ll be certain the results match what you need. The boolean data type, hence bool, is the same logical AND (&&) operator in the computer programming language.

Here’s an example shown in JSON format. It illustrates how we can set up a dataset to see the bool data type in action. We have a hypothetical supermarket. The index is store. We have a data type in the index named products. It lists the items available for sale in the store. The fields go across from left to right, starting with the header columns for “id” through “department.”

idnamepricequantitydepartment
1Multi-Grain Cereal4.994Packaged Foods
21lb Ground Beef3.9929Meat and Seafood
3Dozen Apples2.4912Produce
4Chocolate Bar1.292Packaged FoodsCheckout
51 Gallon Milk3.2916Dairy
60.5lb Jumbo Shrimp5.2912Meat and Seafood
7Wheat Bread1.295Bakery
8Pepperoni Pizza2.995Frozen
912 Pack Cola5.296Packaged Foods
10Lime Juice0.9920Produce
1112 Pack Cherry Cola5.595Packaged Foods
121 Gallon Soy Milk3.3910Dairy
131 Gallon Vanilla Soy Milk3.499Dairy
141 Gallon Orange Juice3.294Juice

This is how it is outlined for our mapping order:

curl -H "Content-Type: application/json" -XPUT 127.0.0.1:9200/store -d '
{
"mappings": {
"products": {
"properties" : {
"name": { "type": "text"},
"price": { "type": "double"},
"quantity": { "type": "integer"},
"department": { "type": "keyword"}
}
}
}
}
'

Now we’re ready to conduct a query with multiple criteria. We want to show only products with a price of “less than $1.00” located in the “Produce” department. We already know the answer: lime juice. Let’s test it.

curl -H "Content-Type: application/json" -XGET "127.0.0.1:9200/store/_search?pretty" -d '
{
"query": {
"bool": {
"must": {"match": {"department": "Produce"}},
"filter": { "range": {"price": {"lt": 1.00}}}
}
}
}
'

{
"took" : 70,
"timed_out" : false,
"_shards" : {
"total" : 5,
"successful" : 5,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : 1,
"max_score" : 1.3862944,
"hits" : [
{
"_index" : "store",
"_type" : "products",
"_id" : "10",
"_score" : 1.3862944,
"_source" : {
"id" : "10",
"name" : "Lime Juice",
"price" : 0.99,
"quantity" : 20,
"department" : [
"Produce"
]
}
}
]
}
}

We’re correct. Lime juice it is. That’s because we used the bool data type to combine multiple criteria. There are four types of occurrences in bool.

Three are perceptive and the fourth, not so much. The intuitive ones are filter, must, must_not. The last one is should, and it isn’t relevant for our purposes here. Just to satisfy your curiosity though, the should occurrence has no effect on querying multiple criteria unless you’re trying to define a relevance score. Even in that case, to add more attributes, the filter occurrence may be better suited.

The bool occurrences we use most often are:

  • must – Our criteria must state what the results must have. In the above example, we queried the department for “Produce” only.

  • must_not – By contrast, we could omit all Produce in the results by using the following schema.

"must_not": {"match": {"department": "Produce"}},
  • filter let us use a range to filter our results.
    "filter": { "range": {"price": {"lt": 1.00}}}

You can keep filtering using the occurrences in bool as needed. It’s the easiest way to drill down and return your desired results when making queries with multiple criteria.

Conclusion

Queries aren’t just singular. It’s helpful to make queries with multiple criteria every day to understand our target markets, upsell to existing clients, and discover new opportunities for expanding business in undiscovered territories. This tutorial was designed to show you the possibilities of querying in Elasticsearch using bool. Find out more about the bool data type and Elasticsearch by reading some additional documentation.

Just the Code

Here’s the code if you already know about querying with multiple criteria and want to see an example in Elasticsearch.

curl -H "Content-Type: application/json" -XGET "127.0.0.1:9200/store/_search?pretty" -d '
{
"query": {
"bool": {
"must": {"match": {"department": "Produce"}},
"filter": { "range": {"price": {"lt": 1.00}}}
}
}
}

Pilot the ObjectRocket Platform Free!

Try Fully-Managed Redis,
MongoDB & Elasticsearch

Get Started

OR

Try CockroachDB
in Beta

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.