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]