How to rewrite query for elasticsearch

Question

I have SQL query

SELECT * FROM requests WHERE order_type = 'buyer' AND (plantations_id = 402 OR plantations_id = 460)

My query for elastic is

GET /requests/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "order_type": "buyer"
          }
        },
        {
          "bool": {
            "should": [
              {
                "terms": {
                  "plantations_id": [402, 460]
                }
              }
            ]
          }
        }
      ]
    }
  }
}

But the result set has only items with "plantations_id": "460". I'm a little puzzled how to rewrite my origin query properly.

Thanks in advance.

Answer 1

You don't need the bool/should clause, the correct query is this one:

{
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "order_type": "buyer"
          }
        },
        {
          "terms": {
            "plantations_id": [
              402,
              460
            ]
          }
        }
      ]
    }
  }
}

Or better, move the terms filter to bool/filter instead, since that won't participate in the scoring:

{
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "order_type": "buyer"
          }
        }
      ],
      "filter": [
        {
          "terms": {
            "plantations_id": [
              402,
              460
            ]
          }
        }
      ]
    }
  }
}

Answer 2

Update your query as below

{ 
  "query": {
   "bool": { 
    "must": [
      { "match": { "order_type": "buyer" } },
      { "bool": {
        "should": [
        {
           "terms": {
             "plantations_id": [402, 460]
           }
         }
       ]
      }
     }
    ]
   }
  }
}