Joining and mapping

Hi guys!

I have a relational Database which I have synchronised with Elasticsearch over logstash. I have a work table, article table and comment table. One work has multiple comments and articles, basically work is parent; comments and articles are children. My question is which mapping would you recommend. I have tried to save articles and comments as nested objects but the problem is the amount of comments and articles is veryyy very high. Because only one work hundreds of comments and articles. My actual goal is to find a work by work properties, article title and comment title. So I some how need to join them but in a efficient way. Because to save data that way make my system very slow: you could imagine one work with hundreds of comments und articles and if you want to search after a work the system will have very fun to find those.

put work
"mappings": {

"properties": {
      "articles": {"type": "nested"}
      "comments": {"type": "nested"}


That’s basically my mapping. The properties of each object is going to be generated dynamically with logstash. But the problem is that with this mapping it works even slower than relational db.

I have also tried to store the data so that each object is an index. But in that way I would need to find the right articles and comments get their work id and give those works as result. It would make my search operation two cycles. Which I don’t want, because I am implementing it in Java.

I want to find works over article title and comment title instantly.

Can anybody tell me how to join these three objects in an efficient way. So I can find a work over its article title and comment title.

I am really having some trouble. I really don’t know how to store my data so that I can find works over article title or comment title. I would be very happy if you could help me.:pray:t2::pray:t2::pray:t2::pray:t2::pray:t2::pray:t2::pray:t2:

I need it for work.