How to write the elastic search query for this?

Question

How do we write a query with following conditions:

1. status MUST NOT be "INACTIVE"
2. searchString MUST be "some_String"
3. adsPurchased MUST to be true
4. must have "Product 1" under productCost
5. Sort them based on cost of "Product 1"

Data structure:

  "name": "Ross",
  "status": "ACTIVE",
  "adsPurchased": true,
  "searchString": "some_tring",
  "productCost": [
    {
      "product": "Product 1",
      "cost": "50.0"
    },
    {
      "product": "Product 2",
      "cost": "80.0"
    }
  ]
}

{
  "name": "Chandler",
  "status": "INACTIVE",
  "adsPurchased": true,
  "searchString": "some_String",
  "productCost": [
    {
      "product": "Product 1",
      "cost": "60.0"
    },
    {
      "product": "Product 4",
      "cost": "800.0"
    }
  ]
}

{
  "name": "joey",
  "status": "ACTIVE",
  "adsPurchased": true,
  "searchString": "some_tring",
  "productCost": [
    {
      "product": "Product 1",
      "cost": "30.0"
    },
    {
      "product": "Product 5",
      "cost": "90.0"
    }
  ]
}


So, I should get Ross and Joey

Answer 1

Mapping:

{
  "mappings": {
    "properties": {
      "name": {
        "type": "text"
      },
      "status": {
        "type": "text"
      },
      "adsPurchased": {
        "type": "boolean"
      },
      "searchString": {
        "type": "text"
      },
      "productCost": {
        "type": "nested",
        "properties": {
          "product": {
            "type": "text"
          },
          "cost": {
            "type": "integer"
          }
        }
      }
    }
  }
}

Search Query:

{
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "adsPurchased": true    -->adsPurchased MUST to be true
          }
        },
        {
          "match": {
            "searchString": "some_tring"    -->searchString MUST be "some_tring"
          }
        },
        {
          "nested": {
            "path": "productCost",
            "query": {
              "bool": {
                "must": [           -->must have "Product 1" under productCost
                  {
                    "match": {
                      "productCost.product": "Product 1"  
                    }
                  }
                ]
              }
            }
          }
        }
      ],
      "must_not": [
        {
          "match": {
            "status": "INACTIVE"    -->status MUST NOT be "INACTIVE"              
            }
        }
      ]
    }
  },
  "sort": [                                 -->Sort them based on cost
    {
      "productCost.cost": {
        "order": "asc",
        "nested_path": "productCost"
      }
    }
  ]
}

Search Result :

"hits": [
      {
        "_index": "foo3",
        "_type": "_doc",
        "_id": "2",
        "_score": null,
        "_source": {
          "name": "joey",
          "status": "ACTIVE",
          "adsPurchased": true,
          "searchString": "some_tring",
          "productCost": [
            {
              "product": "Product 1",
              "cost": "30.0"
            },
            {
              "product": "Product 5",
              "cost": "90.0"
            }
          ]
        },
        "sort": [
          30
        ]
      },
      {
        "_index": "foo3",
        "_type": "_doc",
        "_id": "1",
        "_score": null,
        "_source": {
          "name": "Ross",
          "status": "ACTIVE",
          "adsPurchased": true,
          "searchString": "some_tring",
          "productCost": [
            {
              "product": "Product 1",
              "cost": "50.0"
            },
            {
              "product": "Product 2",
              "cost": "80.0"
            }
          ]
        },
        "sort": [
          50
        ]
      }
    ]

In the search result, you get your desired result i.e Ross and joey

To know more about nested sorting, you can refer to this official documentation and for nested queries refer this

Answer 2

There are basically two ways of doing this.

1. By using a bool query. in this type of query you can add multiple boolean operators in your query ( must, must_not,should) and then and add multiple types of clause in each operator, something like this

2. Using queryString. Elasticsearch provides you way of writing a query with multiple queries with different clauses inside a string

Bool query:

POST _search
{
  "query": {
    "bool" : {
      "must" : {
        "term" : { "user" : "kimchy" }
      },
      "filter": {
        "term" : { "tag" : "tech" }
      },
      "must_not" : {
        "range" : {
          "age" : { "gte" : 10, "lte" : 20 }
        }
      },
      "should" : [
        { "term" : { "tag" : "wow" } },
        { "term" : { "tag" : "elasticsearch" } }
      ],
      "minimum_should_match" : 1,
      "boost" : 1.0
    }
  }

Query string:

GET /_search
{
    "query": {
        "query_string" : {
            "query" : "field1:value1 AND (field2:value2 or field2:value3) and  value4 and key5:(value6 or value7)",
            "default_field" : "content"
        }
    }
}

Value 4 will be found in default_field mentioned below

Answer 3

You need something like this must and must_not inside filter and sort, I used filter context because it is faster, you can use query context if you need. See the difference between query and filter context: https://www.elastic.co/guide/en/elasticsearch/reference/current/query-filter-context.html

GET /your_index_name/_search
{
   "query" : {
      "constant_score" : { 
         "filter" : {
            "bool" : {
              "must" : [
                 { "term" : {"searchString" : "some_String"}}, 
                 { "term" : {"adsPurchased" : true}},
                 { "nested":
                      {
                        "path": "parent","query": {
                           "match": {"productCost.cost": "Product 1"}
                         }
                      }
                 }
              ],
              "must_not" : {
                 "term" : {"status" : "INACTIVE"} 
              }
           }
         }
      }
   }, 
    "sort" : [
    {
      "productCost.cost" : {
         "order" : "asc",
         "nested": {
            "path": "productCost"
         }
      }
    }
  ]
}