Elasticsearch support join on tables or not?

(Hasti Ghobakhloo) #1

iam really confiused . if elasticsearch have not join what is this plugins that can support join on elasticsearch for example:

someone explain more about join in elasticsearch.

(Mark Walkom) #2

Elasticsearch has no SQL-like joins.
Plugins like this one do it in memory, which can lead to scalability restrictions.

(Hasti Ghobakhloo) #3

hi thanx .
but join for programmers is very usefull .for example many to many and one to many joins. how elasticsearch support these joins.
excuse me for my question i have not very much experiment in elasticsearch.

(Mark Walkom) #4

Elasticsearch is a NoSQL, distributed system. Distributed joins are not easy to do.

(Hasti Ghobakhloo) #5

elasticsearch is NoSql database and it means not only SQL . and it means its more than SQL. for these reasons i cant beileve that elasticsearch cant support joins....

(Mark Walkom) #6

It means it's different.

(David Pilato) #7

And it means that you need to forget what you learned about SQL and learn how to work with Document databases or document search engines.

Basically, you need probably to denormalize your documents.

May be we can help if you explain the kind of data you have, like with some examples and why do you think you need "join"?

(Hasti Ghobakhloo) #8

i have a model : CmsCard.java
that this model has this field :

private List<CmsCardSecondaryAccount> subDeposits = new ArrayList<CmsCardSecondaryAccount>();

in my CmsCardSecondaryAccount.java model i have id , code,name, cardId and ....

in my java code i have a search method. i wanna search cards that the name or code of their CmsCardSecondaryAccount is something for example code = 123 and name = "debit".

i first load all CmsCardSecondaryAccount that has same CardId and then with queries in elasticsearch do my solution.but i wanna handle this with something like joinQuery...and something in elasticsearch .i dont want handle this in my bussiness.

(David Pilato) #9

The main question you have to ask yourself are:

  • What my users are looking for?
  • What are the fields they need to search for those objects

If your users are searching for deposits, then index deposits. If a user needs to find a deposit where the card name is foo, then just add to the deposit object the field "card_name": "foo" and you're done.

I'm not saying that it's easy but when you want to use a technology, you need to embrace how it has been designed.

Actually that was my past experience when I added elasticsearch on top of a relational DB with the same kind of Java code behind the scene.

My 2 cents

(Hasti Ghobakhloo) #10

thanx for reply.but i have my problem yet... what kind of join elasticsearch support??? ok i dont look at elasticsearch with my vision of SQL . i know it has document base search. how can we join in document base search??
in parent and child filters that in learn of elasticsearch site said they are join queries what are those?? how can use those queries as join query???

(David Pilato) #11

Yes you can use parent / child feature.

You can read this as an introduction https://www.elastic.co/guide/en/elasticsearch/guide/2.x/parent-child.html But it's not compatible with 6.x yet. So then read https://www.elastic.co/guide/en/elasticsearch/reference/current/parent-join.html

(system) #12

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.