How can i combine the information of two indexes with kibana lens?

[alprde (Musa Demir)]

I have two indexes named market place and stalls. How many stalls in which market place? How can I show this with kibana lens?

marketplace fields:

• id
• name

stalls fields:

• id
• stall_no
• marketplace_id

[ylasri (Yassine LASRI)]

The only way is to join them before ingest to elasticsearch
That should not be a big deal check what is an enrichment policy in elasticsearch to do that...

[alprde (Musa Demir)]

Thank you for the answer. I am importing the data with logstash. Then do I need to merge and add the mysql tables?

[ylasri (Yassine LASRI)]

In that case change your sql query to join all needed tables in one query

[alprde (Musa Demir)]

Is there a simpler way to do this? Because I have to restart logstash on every new query. logstash is running on docker.

[ghudgins (Graham Hudgins)]

If you're visualizing the field that "joins" the 2 indexes together ("marketplace_id" / "id" in your case) then using multiple layers allows you to logically join them around a similar ID

POST stores/_doc
{
  "id": 1,
  "name": "marketplace1"
}

POST stalls/_doc
{
  "id": 100,
  "store_id": 1,
  "name": "stall 1 in marketplace1"
}

Not sure if that works for you or not as you'd have to visualize the ID....

[alprde (Musa Demir)]

Thanks for your answer, but this is not working for me. I will try to insert merge data with mysql.