One-to-Many Relationships in Elastic Search

Question

Suppose I have 2 tables called twitter_user and twitter_comments.

twitter_users has fields: username and biography twitter_comments has fields: username and comment

Obviously, the user has 1 post on twitter_users and potentially a lot on twitter_comments

I want to simulate both twitter_users and twitter_comments in Elastic Search, when searching in a query I search for ES for both models, knowing that the comment takes into account the general relevance score for the Twitter user.

I know that I can imitate this with only one model by creating one additional field (besides the username and bio) with all the comments combined. But is there another “clean” way?

Answer 1

It depends.

If you just want to be able to search for user comments, full-text and all fields, just save all the comments in the user object (there is no need to concatenate anything):

{ "user" : { "username" : "TestUser", "bio" : "whatever", "comments" : [ { "title" : "First comment", "text" : "My 1st comment" }, { "title" : "Second comment", "text" : "My 2nd comment" } ] } } 

If you need comments-based queries, you need to map the comments as nested (before sending any data) so that each comment is treated as a single element.

For your winnings just add another field "comment_count" and use it to increase / count points.

Answer 2

As Torsten already said, you can use a subquery, and this is a good approach.

In addition, you can index comments as child users. Then you can search for users just like now, search for comments using top_children to find everything that suits your search comments, and finally combine ratings from both of them together using bool or dis_max queries.

Nested search will be more effective during the search, but you will have to re-index the user and all comments each time an additional comment is added. With the child / parent approach, you will only need to index new comments, but the search will be slower and this will require more memory.