ES|QL LOOKUP JOIN between fields containing a list of values

Elastic Stack Elasticsearch
1

Hi all,

creating a LOOKUP JOIN query is working fine, but I am not getting results when I have to do it between 2 fields containing a list of values.

For example :

  • main index field = "sizes" : ["S", "M"]
  • lookup index field = "sizes" : ["S","M","L"]

I want a hit if "S" or "M" exist in the lookup index, but I keep getting no result. Could it be because list fields are not supported ?

Kind regards.

2

I think that is the reason, according to the documentation.

If using join on a single field or a field list, the fields used must exist in both your current query results and in the lookup index. If the fields contains multi-valued entries, those entries will not match anything (the added fields will contain null for those rows).

For the main index you can use MV_EXPAND to expand the multi-valued field, but for your lookup index you will need to change it so the field has one single value.

2 Likes
3

That's exactly it! Thanks for your solution.