How to inner join 3 index

[Gian_Carlo_Menotti]

Hi, i need to inner join 3 index in elastic for crate 1, is this possible? where can i found documentation, the 3 tables are in already in elastic
the results should be something like this

Select a.IdEntry, b.ItemCode, c.Date
from
TableA as a
inner join
TableB as b on a.idEntry = b.idEntry
inner join
TableC as c on b.itemCode = c.itemCode

here is my sample

[Christian_Dahlqvist]

Elasticsearch does not support joins, so you are probably better off altering your data model, denormalising your data.

[Gian_Carlo_Menotti]

Thanks for your reply, can i cant create parent/child with the data is already indexed?

[Christian_Dahlqvist]

Parent-child relationships in Elasticsearch is one way to model a relationship, but is not a generic join. It also requires special mappings and that all related data are located in the same shard, so would require reindexing.

I would recommend rethinking you data model with a document centric view rather than try to implement some kind of relational model.

[Gian_Carlo_Menotti]

Thx for your advices, one more question, i have 3 tables in sql, with more than 10 million of records on each one, i'm using the jdbc driver to move data to to elastic, what do you recommend to do for a view that takes 45 minutes to retrieve the results. in the view i use ( MIN, MAX, HAVING, GROUPING)

[Christian_Dahlqvist]

I am afraid I won't be able to help you optimise that query so it runs faster if that is the problem. If the issue is with Logstash processing the data, it can not go faster than the slowest downstream system, so you may want to look at the throughput your destinations support. 10 million over 45 minutes is just 3.7k EPS, which does not sound much for Logstash, but it may help if you share your config so we can see if there are any issues with it.

[Gian_Carlo_Menotti]

ok this is my scenario, i denormalize my 3 tables to 1 table, then i move it to elastic without my filters (see Picture B) because if i put it on the statement it will not work (see picture A). What i need is to replicate the query on elastic, is this possible? or what other suggestion would you recommend.

(Picture A)

(Picture B)

[Gian_Carlo_Menotti]

any advice?

[Christian_Dahlqvist]

I don't know the data model nor the problem you are looking to solve, so do not really have any suggestions.