how can I write this mysql query in elasticsearch ?
SELECT *,count(products.store_id)
FROM stores
INNER JOIN products
ON stores.store_id=products.store_id
group by stores.store_id;
Add a comment
|
1 Answer
Elasticsearch is not exactly a database, it's a search engine and hence it supports very limited JOIN operations (parent-child queries).
If you want to execute the above query then you will have to rework the schema and try to have the data in one index (doesn't matter even if it's not in 2NF/3NF). Maybe you can index store_id along with each product document.
Now, coming back to the query, if you want to execure the above query on let's say one index then you can do it using TERMS aggregation. It will give you count of products grouped by store id, the request would look like this:
$ curl -XPOST 'http://localhost:9200/products/_search?search_type=count' -d '{
"query" : {
"filtered" : {
"filter" : {
"bool" : {
"must" : [
{ "term" : {"product_type" : "sometype"}}
]
}
}
}
},
"aggs" : {
"products" : {
"terms" : {
"field" : "store_id"
}
}
}
}'
