Range based join between two indices?

Elastic Stack Elasticsearch
1

I have two indices:

  • IndexA with field Date_time
  • Index B with fields MoveInDateTime and MoveOutDateTime

I want to join them on MoveInDateTime <= Date_time <= MoveOutDateTime.

Is this possible in Elasticsearch? If not, what are some alternative approaches?

Thank you very much!

2

Welcome!

I don't think you can do that. Even the new LOOKUP JOIN of ES|QL won't help with that.

An alternative approach would be by programmatically run:

  • The search query on IndexA
  • Iterate on all the results (up to 10000 - otherwise you need to use deep pagination)
  • For each result, run a search query on indexB
  • Join on the client side both informations...

My 2 cents