how can I write the below sql query in elastic search?

Question

SELECT * FROM products WHERE (stock_code = '1234' AND category_code = '3214') OR (stock_code = '1234' AND category_code = '3214')

Also I want named query to be used bcz in the result matched query is needed

I have this now

$params['body']['query']['bool']['must']['bool']['should'][]=[
                'match' => [
                    'stock_code' => [
                        'query'     => $val->stock_code,
                        '_name'      => $val->stock_code,
                    ],
                ]
            ];

Answer 1

You need do it this way using two bool/filter (i.e. inner AND) inside a bool/should (i.e. outer OR):

{
  "query": {
    "bool": {
      "minimum_should_match": 1,
      "should": [
        {
          "bool": {
            "filter": [
              {
                "term": {
                  "stock_code": {
                    "value": "1234",
                    "_name": "query1"
                  }
                }
              },
              {
                "term": {
                  "category_code": {
                    "value": "1234",
                    "_name": "query2"
                  }
                }
              }
            ]
          }
        },
        {
          "bool": {
            "filter": [
              {
                "term": {
                  "value": "5678",
                  "_name": "query3"
                }
              },
              {
                "term": {
                  "category_code": {
                    "value": "5678",
                    "_name": "query4"
                  }
                }
              }
            ]
          }
        }
      ]
    }
  }
}