Conditional query in elasticsearch

Question

I have one conditional query in mysql i want to convert it in elasticsearch query :

Query :

I have product list page with price ranger search. If user use that search I want to check if product has sale then it should consider sale price else selling price. (check sale : i am checking sale by current date between sale_start and sale_end date.)

MySql Query :

SELECT *
FROM `product_sku`
WHERE 
((sale_start < '2016-05-12 15:23:53' AND sale_end > '2016-05-12 15:23:53' AND sale_price between 600 AND 1800)
 OR (sale_end < '2016-05-12 15:23:53' AND selling_price between 600 AND 1800) 
)

Elasticsearch Query :

$params = [
            'index' => 'index',
            'type' => 'product-list',
            'body' => [
                "query" => [
                    "filtered" => [
                        "query" => [
                            "match_all" => [],
                        ],
                        'query' => $query,

                        "filter" => [
                            "nested" => [
                                "path" => "default_product_low_price_with_seller",
                                "filter" => [
                                    "bool" => [
                                        "should" => [
                                            [
                                                "range" => [
                                                    "default_product_low_price_with_seller.sale_price" => [
                                                        "gte" => $_GET['filter']['price']['from'],
                                                        "lte" => $_GET['filter']['price']['to'],
                                                    ],
                                                ],
                                            ],
                                            [
                                                "range" => [
                                                    "default_product_low_price_with_seller.sale_end" => [
                                                        "gte" => $now,
                                                    ],
                                                ],
                                            ],
                                            [
                                                "range" => [
                                                    "default_product_low_price_with_seller.sale_start" => [
                                                        "lte" => $now,
                                                    ],
                                                ],
                                            ],

                                        ],
                                    ],
                                ],
                                "filter" => [
                                    "bool" => [
                                        "should" => [
                                            [
                                                "range" => [
                                                    "default_product_low_price_with_seller.selling_price" => [
                                                        "gte" => $_GET['filter']['price']['from'],
                                                        "lte" => $_GET['filter']['price']['to'],
                                                    ],
                                                ],
                                            ],
                                            [
                                                "range" => [
                                                    "default_product_low_price_with_seller.sale_end" => [
                                                        "lte" => $now,
                                                    ],
                                                ],
                                            ],
                                            [
                                                "range" => [
                                                    "default_product_low_price_with_seller.sale_start" => [

                                                        "gte" => $now,
                                                    ],
                                                ],
                                            ],

                                        ],
                                    ],
                                ],
                            ],
                        ],
                    ],
                ],
                "aggs" => [
                    "brand_name" => ["terms" => ["field" => "brand_name"]],
                    "category_with_in_title" => [
                        "nested" => [
                            "path" => "category_with_in_title.parent_cat",
                        ],
                        "aggs" => [

                            "category_with_in_title.title" => ["terms" => ["field" => "category_with_in_title.parent_cat.title"]],
                        ],
                    ],
                ],
            ],
        ];

Answer 1

Try this query:

{
  "query": {
    "nested": {
      "path": "default_product_low_price_with_seller",
      "query": {
        "bool": {
          "should": [
            {
              "bool": {
                "must": [
                  {
                    "range": {
                      "default_product_low_price_with_seller.sale_price": {
                        "gte": 100,
                        "lte": 300
                      }
                    }
                  },
                  {
                    "range": {
                      "default_product_low_price_with_seller.sale_start": {
                        "lte": "2016-05-05"
                      }
                    }
                  },
                  {
                    "range": {
                      "default_product_low_price_with_seller.sale_end": {
                        "gte": "2016-05-05"
                      }
                    }
                  }
                ]
              }
            },
            {
              "bool": {
                "must": [
                  {
                    "range": {
                      "default_product_low_price_with_seller.selling_price": {
                        "gte": 100,
                        "lte": 300
                      }
                    }
                  },
                  {
                    "range": {
                      "default_product_low_price_with_seller.sale_end": {
                        "lte": "2017-05-05"
                      }
                    }
                  }
                ]
              }
            }
          ]
        }
      }
    }
  }
}

So, the two conditions are branches of a global should list and under each branch there is a series of must statements under a new bool query.