one-to-many relationships in Elastic Search

Question

Suppose I have 2 tables called "twitter_user" and "twitter_comments".

twitter_users has the fields: username and bio twitter_comments has the fields: username and comment

Obviously, an user has 1 entry in twitter_users and potentially many in twitter_comments

I want to model both twitter_users and twitter_comments in Elastic Search, have ES search both models when I query, knowing that a comment counts towards the overall relevancy score for a twitter user.

I know I can mimic this with just 1 model, by creating a single extra field (in addition to username and bio) with all the comments concatenated. But is there another "cleaner" way?

Answer 1

It depends.

If you just want to be able to search for a users comments ,full-text and over all fields, simply store all comments within the user object (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 per-comment-based queries you need to map the comments as nested (before submitting any data), so that every comment gets treated as a single item.

For your scoring, simply add another field "comment_count" and use this for your boost/scoring.