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

[jamesweb1 (James)]

In my application, I have two indices like:

Elasticsearch (index: 'identify_results')

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')

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?

[dadoonet (David Pilato)]

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

[jamesweb1 (James)]

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.

[dadoonet (David Pilato)]

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
• ...