# Join the two index in the ElasticSearch to query the data with sorting

**URL:** <https://discuss.elastic.co/t/join-the-two-index-in-the-elasticsearch-to-query-the-data-with-sorting/294519>\
**Category:** Elasticsearch\
**Tags:** eql-elastic-query-language\
**Created:** [January 17, 2022, 3:24am UTC](https://discuss.elastic.co/t/join-the-two-index-in-the-elasticsearch-to-query-the-data-with-sorting/294519 "2022-01-17T03:24:51Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![jamesweb1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jamesweb1/32/97934_2.png) [@jamesweb1](https://discuss.elastic.co/u/jamesweb1)\
**Post date:** [January 17, 2022, 3:24am UTC](https://discuss.elastic.co/t/join-the-two-index-in-the-elasticsearch-to-query-the-data-with-sorting/294519/1 "2022-01-17T03:24:51Z")

</div>

In my application, I have two indices like:

### Elasticsearch (index: 'identify\_results')

```auto
deviceId value time
00245765 100 2021-11-22T08:20:01.004Z
10245888 200 2021-11-23T10:20:01.004Z
00245765 300 2021-11-24T11:20:01.004Z
00245765 100 2021-11-25T04:20:01.004Z
10245888 900 2021-11-26T03:20:01.004Z
10245888 450 2021-11-26T04:20:01.004Z
20245888 500 2021-11-27T08:20:01.004Z

```

### Elasticsearch (index: 'device\_name\_mapping')

```auto
deviceId deviceName
00245765 Device_C
10245888 Device_A
20245888 Device_B

```

Obviously, index 'identify\_results' stores the data and index 'device\_name\_mapping' stores the property of this device. How can I query the 'identify\_results' and sort them by the deviceName in the Elasticsearch? I found Elasticsearch is NoSQL database, one approach is I move 'deviceName' into the index 'identify\_results', but It will be very trouble when I update the deviceName in the future. How can I achieve it in the Elasticsearch?

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [January 17, 2022, 3:46am UTC](https://discuss.elastic.co/t/join-the-two-index-in-the-elasticsearch-to-query-the-data-with-sorting/294519/2 "2022-01-17T03:46:58Z")

</div>

That's the best option though. If this theorical update happens once per year or has less than let say 10m events, I'd probably go that way.

Otherwise, have a look at [Join field type | Elasticsearch Guide [7.16] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/parent-join.html)

---

<div class="post-metadata">

**Author:** ![jamesweb1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jamesweb1/32/97934_2.png) [@jamesweb1](https://discuss.elastic.co/u/jamesweb1)\
**Post date:** [January 18, 2022, 2:57am UTC](https://discuss.elastic.co/t/join-the-two-index-in-the-elasticsearch-to-query-the-data-with-sorting/294519/3 "2022-01-18T02:57:18Z")

</div>

Thanks for answering. I have read the Join type before, but it looks like both of them should be in the same index. In my application, I have many index 'identify\_results' but only one index 'device\_name\_mapping' so that It looks line not suitable for me.

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [January 18, 2022, 3:23am UTC](https://discuss.elastic.co/t/join-the-two-index-in-the-elasticsearch-to-query-the-data-with-sorting/294519/4 "2022-01-18T03:23:18Z")

</div>

Yes. That's the way you need to reindex your data.  
Elasticsearch is not a relational database but a search engine.

As I said, ideally, I'd use only one document which contains everything including the device name.

Again, how often are you doing to rename a device? What is the cost of reindexing all data for this device?  
Depending on your real use case, you could have multiple choices:

- all in one doc
- join type
- use a relational database
- ...

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [February 15, 2022, 3:23am UTC](https://discuss.elastic.co/t/join-the-two-index-in-the-elasticsearch-to-query-the-data-with-sorting/294519/5 "2022-02-15T03:23:59Z")

</div>

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