Join on ElasticSearch

Elastic Stack Elasticsearch
1

Hello ElasticSearch experts,

If I want to do inner join of two query result sets, and wondering if any good examples or documents to refer to? I mean if inner join could be done by ElasticSearch itself of the two sub-query so that I can fetch final results directly? Thanks.

thanks in advance,
Lin

1 Like
2

You cannot do joins in ES. They need to be done with external code.

1 Like
3

@warkolm, could you provide a bit more details what are the external code do you mean and their basic logics? Thanks.

4

There is a Join like feature I'd say with Parent/Child feature but may be not what you are looking for. And it comes with a memory cost.

If you are coming from a relational model , you' better have to rethink your model instead of trying to apply the same concepts in a Document oriented search engine.

My cents.

1 Like
5

@dadoonet, good points and confused how Parent/Child feature is related to join? More hints are appreciated.

6

Well, Parent/Child does join in memory between one parent and the children documents attached to it.

So you can run queries like:

{
    "has_parent" : {
        "parent_type" : "blog",
        "query" : {
            "term" : {
                "tag" : "something"
            }
        }
    }
}

Read this excellent chapter for details: https://www.elastic.co/guide/en/elasticsearch/guide/current/parent-child-mapping.html

2 Likes
7

@dadoonet, thanks but I still feel lost how Parent/Child join related to normal database join concept, suppose I have a customer table and another order table, and I want to join by customer ID to find customer who has ordered something. How to implement that with Parent/Child join? Thanks.

8

Create a customer doc.
Then create order documents with the customer as the parent doc.

But as I said, don't apply relational model in document oriented system. Re-think your model based on your use case.

1 Like
9

@dadoonet, I think with Parent/Chid model, we can do exact what relational model support? Are there any join cases you think Parent/Child model cannot cover, comparing to relational inner join case? Thanks.

10

May be. But again, forget all you know about relational databases and start learning document oriented data models. They are really different beasts.

For example, I'd most likely copy in "order" document all information about the "customer". Instead of trying to link the entities.

2 Likes
11

@dadoonet, thanks a lot and these days, I have some new thinkings and let us discuss there => Parent/child document join, looking forward to your help and guidance. :smile:

regards,
Lin