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

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?

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

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.

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

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