Elasticsearch seach using wildcard, range and terms

I am trying to write a search DSL query on a Elasticsearch ( Version 7.1 ) index that has fields like, product_name, exported_at and category_id.

From the document I understand that I would need to use the wildcard, range to search on the date field and terms to filter the required category_id.

Example: I need to filter the data whose SQL would be:

SELECT * from products table
WHERE product_name LIKE '%geography%'
AND exported_at >= '2020-01-01' AND exported_at <= '2020-12-31'
AND category_id IN [10,20,30,40]

The DSL query that I build in PHP is:

    $params = [
                'index' => ExportData::ELASTIC_INDEX,
                'type' => ExportData::ELASTIC_TYPE,
                'from' => $start,
                'size' => $length,
                'body' => [
                    'query' => [
                        'wildcard' => [
                                'product_name' => [
                                'value' => "*".$keyword."*", 
                                'boost' => 1.0,
                                'rewrite' => 'constant_score'
                            ],
                        ],

                        "range" => [
                            "exported_on" =>  [
                                "gte" => $requestVars['date_from'],
                                "lte" => $requestVars['date_to'],
                                "boost" => 2.0
                            ]
                        ],
                        "terms" => [
                            "category_id" => [10,20,30,40],
                            "boost" => '1.0'
                        ]
                    ],
                 ]
            ];

However, as expected, this fails with the error:

"message": "{\"error\":{\"root_cause\":[{\"type\":\"parsing_exception\",\"reason\":\"[wildcard] malformed query, expected [END_OBJECT] but found [FIELD_NAME]

Hi @lalitesh , I think that you need use bool query:

You can use the SQL translate API to see the query DSL.