CODEWITHSUNDEEP
mysqlelasticsearch
Rakesh
Rakesh

Reputation: 37

how can I write this query in elasticsearch?

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;

Upvotes: 1

Views: 56

Answers (1)

Darshan Mehta
Darshan Mehta

Reputation: 30849

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"
            }
        }
    }
}'

Upvotes: 1

Related Questions