Join documents in elasticsearch

Question

I have these product documents:

curl -XPOST -H 'Content-Type: application/json' "http://localhost:9200/test/_bulk?pretty" -d'
{"index":{"_index":"test","_type":"product"}}
{"id":"1", "price": "260" }
{"index":{"_index":"test","_type":"product"}}
{"id":"2", "price": "420" }
{"index":{"_index":"test","_type":"product"}}
{"id":"3", "price": "250" }

And these discount documents:

curl -XPOST -H 'Content-Type: application/json' "http://localhost:9200/test/_bulk?pretty" -d'
{"index":{"_index":"test","_type":"discount"}}
{"product_id":"1", "group_id": 12, "percent": "50" }
{"index":{"_index":"test","_type":"discount"}}
{"product_id":"1", "group_id": 15, "percent": "20" }
{"index":{"_index":"test","_type":"discount"}}
{"product_id":"3", "group_id": 12, "percent": "10" }

I need to join these two documents via the product_id and group_id key.

In the mysql database would look like this:

SELECT p.id, IF(d.percent > 0, p.price - (d.percent*p.price/100), p.price) price 
FROM product p
LEFT JOIN discount d ON p.id=d.product_id AND d.group_id=12
WHERE IF(d.percent > 0, p.price - (d.percent*p.price/100), p.price) < 210

The result of that query will be only one row:

id => 1
price => 130

Is it possible to do in elasticsearch?

This might help: https://stackoverflow.com/questions/36915428/how-to-setup-elasticsearch-index-structure-with-multiple-entity-bindings/36982705#36982705 — Val, Jun 25 '18 at 13:09
Elasticsearch does not do joins between indices and supporting multiple types per index has been deprecated and should produce an error in recent versions of Elasticsearch. You might be able to denormalize discounts into the products and do it that way, though. For that, look into using nested queries and aggregations. — Jilles van Gurp, Jun 25 '18 at 13:18
Possible duplicate of ["Join query" in ElasticSearch](https://stackoverflow.com/questions/31708689/join-query-in-elasticsearch) — Mišo, May 01 '19 at 09:35

Join documents in elasticsearch

0 Answers0