How to setup ElasticSearch index structure with multiple entity bindings
That's a great start!
I would definitely flatten it all out (i.e. denormalize) and come up with product documents that look like the one below. That way you get rid of the N:M relationship between products and flags by simply creating a flags
array for each product. It will thus be easier to query those flags.
{ "id": "00c8234d71c4e94f725cd432ebc04", "title": "Alpha", "price": 589.0, "flags": ["Sellout", "Top Product"]}{ "id": "018357657529fef056cf396626812", "title": "Beta", "price": 355.0, "flags": ["Discount"]}{ "id": "01a2c32ceeff0fc6b7dd4fc4302ab", "title": "Gamma", "price": 0.0, "flags": ["Discount"]}
The product mapping type would look like this:
PUT products{ "mappings": { "product": { "properties": { "id": { "type": "string", "index": "not_analyzed" }, "title": { "type": "string" }, "price": { "type": "double", "null_value": 0.0 }, "flags": { "type": "string", "index": "not_analyzed" } } } }}
Since you have the logstash jdbc
input already, all you're missing is the proper SQL query to fetch the products and associated flags.
SELECT p.Id as id, p.Title as title, p.Price as price, GROUP_CONCAT(f.Title) as flags FROM Products p JOIN flagsProducts fp ON fp.ProductId = p.Id JOIN Flags f ON fp.FlagId = f.idGROUP BY p.Id
Which would get you rows like these:
+-------------------------------+-------+-------+---------------------+| id | title | price | flags |+-------------------------------+-------+-------+---------------------+| 00c8234d71c4e94f725cd432ebc04 | Alpha | 589 | Sellout,Top product || 018357657529fef056cf396626812 | Beta | 355 | Discount || 01a2c32ceeff0fc6b7dd4fc4302ab | Gamma | 0 | Discount |+-------------------------------+-------+-------+---------------------+
Using Logstash filters you can then split the flags
into an array and you're good to go.