With [ES|QL](https://www.elastic.co/docs/explore-analyze/query-filter/languages/esql), Elasticsearch has finally added the possibility to perform actual `JOIN`s on multiple indices. This is available since Elastic 8.18 and called [LOOKUP JOIN](https://www.elastic.co/guide/en/elasticsearch/reference/8.x/esql-commands.html#esql-lookup-join):
``` FROM <source_index> | LOOKUP JOIN <lookup_index> ON <field_name> ```
It might work like this:
(https://i.sstatic.net/UFDt3OED.png)
(Image is from [their blog article](https://www.elastic.co/blog/esql-lookup-join-elasticsearch) on the subject.)
So in your case, the query would be:
``` FROM order | LOOKUP JOIN order_item ON order_id ```
However, the current limitation is that the field names must be the same in both indexes, so you would have to rename your `id` column in `order` to `order_id`.

With [ES|QL](https://www.elastic.co/docs/explore-analyze/query-filter/languages/esql), Elasticsearch has finally added the possibility to perform actual `JOIN`s on multiple indices. This is available since Elastic 8.18 and called [LOOKUP JOIN](https://www.elastic.co/guide/en/elasticsearch/reference/8.x/esql-commands.html#esql-lookup-join):
``` FROM <source_index> | LOOKUP JOIN <lookup_index> ON <field_name> ```
It might work like this:
](https://i.sstatic.net/UFDt3OED.png)
(Image is from [their blog article](https://www.elastic.co/blog/esql-lookup-join-elasticsearch) on the subject.)
So in your case, the query would be:
``` FROM post | LOOKUP JOIN profile ON profile_id ```
However, the current limitation is that the field names must be the same in both indexes.