# Can I use ESQL Lookup Join to match fields with different names?

**URL:** <https://discuss.elastic.co/t/can-i-use-esql-lookup-join-to-match-fields-with-different-names/385324>\
**Category:** Elasticsearch\
**Tags:** esql\
**Created:** [March 3, 2026, 11:31am UTC](https://discuss.elastic.co/t/can-i-use-esql-lookup-join-to-match-fields-with-different-names/385324 "2026-03-03T11:31:05Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![TSlump](https://avatars.discourse-cdn.com/v4/letter/t/ac8455/32.png) [@TSlump](https://discuss.elastic.co/u/TSlump)\
**Post date:** [March 3, 2026, 11:31am UTC](https://discuss.elastic.co/t/can-i-use-esql-lookup-join-to-match-fields-with-different-names/385324/1 "2026-03-03T11:31:05Z")

</div>

Hi,

I have two indices with matching fields that I want to use for an ESQL Lookup Join, however, the fields in each index have different names. Am I still able to use a lookup join?

As an example, I have a ‘customers’ index with a document per customer, and each customer doc has an ‘id’. I also have a ‘purchase-history’ index that includes a ‘customerId’ field. I’d like to use a lookup join here but the fields have different names. I’ve tried:

```auto
FROM purchase-history

| LOOKUP JOIN customers ON customerId == id

```

but this hasn’t worked.

I appreciate any help with this, thank you.

(Elastic+Kibana version 9.2.2)

---

<div class="post-metadata">

**Author:** ![RainTown](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/raintown/32/140206_2.png) [@RainTown](https://discuss.elastic.co/u/RainTown)\
**Post date:** [March 3, 2026, 11:58am UTC](https://discuss.elastic.co/t/can-i-use-esql-lookup-join-to-match-fields-with-different-names/385324/2 "2026-03-03T11:58:02Z")

</div>

there's the EVAL and RENAME ES|QL functions you can use to align the column names.

See also [here](https://www.elastic.co/docs/reference/query-languages/esql/esql-lookup-join) and [here](https://www.elastic.co/search-labs/blog/elasticsearch-esql-lookup-join) .

---

<div class="post-metadata">

**Author:** ![TSlump](https://avatars.discourse-cdn.com/v4/letter/t/ac8455/32.png) [@TSlump](https://discuss.elastic.co/u/TSlump)\
**Post date:** [March 3, 2026, 1:34pm UTC](https://discuss.elastic.co/t/can-i-use-esql-lookup-join-to-match-fields-with-different-names/385324/3 "2026-03-03T13:34:41Z")

</div>

> [@RainTown](#):
>

Thanks for your response.

I’m not sure I’m fully understanding. In the example I gave, would the esql look like:

```auto
FROM purchase-history

| RENAME customerId AS id

| LOOKUP JOIN customers ON id

| RENAME id AS customerId

```

But then we’d lose the initial id field from ‘purchase-history’…

Ideally we would amend the ‘customers’ index to either evaluate a copied customerId field or rename its id to customerId, I don’t see how we can do this with the customers index being the right lookup index. Does esql let us amend the right lookup index before we join it somehow?

---

<div class="post-metadata">

**Author:** ![RainTown](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/raintown/32/140206_2.png) [@RainTown](https://discuss.elastic.co/u/RainTown)\
**Post date:** [March 3, 2026, 2:21pm UTC](https://discuss.elastic.co/t/can-i-use-esql-lookup-join-to-match-fields-with-different-names/385324/4 "2026-03-03T14:21:56Z")

</div>

its easier if you would share a couple of sample documents.

At first glance the query looked OK., but make sure you have the correct index mode, as per the docs I linked, which may require a re-index.

If you have an "id" field in purchase-history, then RENAME that too, before the LOOKUP JOIN, something like:

```auto
FROM purchase-history
| RENAME id AS ph_id
| RENAME customerId AS id
| LOOKUP JOIN customers ON id
| RENAME id AS customerId
| REMAME ph_id AS id

```

If yoy have indices with field names in common, but meaning different, index-dependent things in each, it will get a bit inelegant.

---

<div class="post-metadata">

**Author:** ![leandrojmp](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/leandrojmp/32/107231_2.png) [@leandrojmp](https://discuss.elastic.co/u/leandrojmp)\
**Post date:** [March 3, 2026, 3:25pm UTC](https://discuss.elastic.co/t/can-i-use-esql-lookup-join-to-match-fields-with-different-names/385324/5 "2026-03-03T15:25:55Z")

</div>

> [@TSlump](#):
>
> ```auto
> FROM purchase-history
> 
> | LOOKUP JOIN customers ON customerId == id
> 
> ```

This should work on 9.2 without any issues.

On the left side you have the field in your source index (`purchase-history`) and on the right side you have the field in your lookup index (`customers`).

Is `customers` set as a lookup index, with `index.mode: lookup`?

---

<div class="post-metadata">

**Author:** ![TSlump](https://avatars.discourse-cdn.com/v4/letter/t/ac8455/32.png) [@TSlump](https://discuss.elastic.co/u/TSlump)\
**Post date:** [March 3, 2026, 3:52pm UTC](https://discuss.elastic.co/t/can-i-use-esql-lookup-join-to-match-fields-with-different-names/385324/6 "2026-03-03T15:52:29Z")

</div>

Thanks @RainTown , looks like that solution would work but I not a fan of moving the docs id to a temp field during the lookup… I can amend the original index mappings though so that both indices have a customerId (the customer’s customerId just being a copy of its id) and that should simplify this.

@leandrojmp yup both indices are lookup indices. I get the following error when I try to use that esql:  
`[esql] > Unexpected error from Elasticsearch: verification_exception - Found ambiguous reference to [id]; matches any of [line 1:1 [id], line 2:15 [id]]`

I think the syntax in general works, but it breaks if it uses a common field that is present in both indices… Feels a bit like a bug to me but I may be using it incorrectly.

---

<div class="post-metadata">

**Author:** ![RainTown](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/raintown/32/140206_2.png) [@RainTown](https://discuss.elastic.co/u/RainTown)\
**Post date:** [March 4, 2026, 12:50pm UTC](https://discuss.elastic.co/t/can-i-use-esql-lookup-join-to-match-fields-with-different-names/385324/7 "2026-03-04T12:50:03Z")

</div>

> [@TSlump](#):
>
> Thanks @RainTown , looks like that solution would work

Yeah, tested as below (tested on 9.3.0), seems to work fine.

> [@TSlump](#):
>
> can amend the original index mappings though so that both indices have a customerId (the customer’s customerId just being a copy of its id) and that should simplify this.

Indeed, also a solution. Often it is not practicable to change mappings on existing data.

> [@TSlump](#):
>
> @leandrojmp yup both indices are lookup indices. I get the following error when I try to use that esql:  
> `[esql] > Unexpected error from Elasticsearch: verification_exception - Found ambiguous reference to [id]; matches any of [line 1:1 [id], line 2:15 [id]]`
> 
> I think the syntax in general works, but it breaks if it uses a common field that is present in both indices… Feels a bit like a bug to me but I may be using it incorrectly.

Either a bug or a feature! See below. It seems the `LOOKUP JOIN <index> ON <x> == <y>` makes no assumption about which of the 2 indices `<x>` is in, and `<y>` is in.

```auto
DELETE /customers-tmp
PUT /customers-tmp
{
  "settings": {
    "index.mode": "lookup",
    "number_of_replicas": 0
  },
  "mappings": {
    "properties": {
      "id": {"type": "long"},
      "name": {"type": "keyword"}
    }
  }
}
DELETE /purchase-history-tmp
PUT /purchase-history-tmp
{
  "settings": {
    "index.mode": "standard",
    "number_of_replicas": 0
  },
  "mappings": {
    "properties": {
      "id": {"type": "keyword"},
      "customerid": {"type": "long"},
      "price" :{"type": "long"}
    }
  }
}
PUT /customers-tmp/_doc/1
{ "id": "1","name": "John"}
PUT /customers-tmp/_doc/2
{ "id": "2","name": "James"}
PUT /customers-tmp/_doc/3
{ "id": "3","name": "Peter"}
PUT /customers-tmp/_doc/4
{ "id": "4","name": "Paul"}
PUT /customers-tmp/_doc/5
{ "id": "5","name": "Tom"}
PUT /customers-tmp/_doc/6
{ "id": "6","name": "Dave"}
PUT /purchase-history-tmp/_doc/1
{"customerId": 1,"id": "order#1","price": "20"}
PUT /purchase-history-tmp/_doc/2
{"customerId": 2,"id": "order#2","price": "25"}
PUT /purchase-history-tmp/_doc/3
{"customerId": 4,"id": "order#3","price": "5"}
PUT /purchase-history-tmp/_doc/4
{"customerId": 4,"id": "order#4","price": "7"}
PUT /purchase-history-tmp/_doc/5
{"customerId": 5,"id": "order#5","price": "11"}
PUT /purchase-history-tmp/_doc/6
{"customerId": 5,"id": "order#6","price": "30"}
PUT /purchase-history-tmp/_doc/7
{"customerId": 1,"id": "order#7","price": "9"}
PUT /purchase-history-tmp/_doc/8
{"customerId": 2,"id": "order#8","price": "4"}
PUT /purchase-history-tmp/_doc/9
{"customerId": 3,"id": "order#9","price": "12"}

```

This works as expected:

```auto
FROM purchase-history-tmp
| RENAME id AS ph_id, customerId AS id
| LOOKUP JOIN customers-tmp ON id
| RENAME id AS customerId, ph_id AS id | SORT customerId ASC

```

Note this also works:

```auto
FROM purchase-history-tmp
| RENAME id AS orderId
| LOOKUP JOIN customers-tmp ON id == customerId

```

and also this:

```auto
FROM purchase-history-tmp
| RENAME id AS orderId
| LOOKUP JOIN customers-tmp ON customerId == id 

```

---

<div class="post-metadata">

**Author:** ![TSlump](https://avatars.discourse-cdn.com/v4/letter/t/ac8455/32.png) [@TSlump](https://discuss.elastic.co/u/TSlump)\
**Post date:** [March 4, 2026, 1:33pm UTC](https://discuss.elastic.co/t/can-i-use-esql-lookup-join-to-match-fields-with-different-names/385324/8 "2026-03-04T13:33:03Z")

</div>

Thanks @RainTown ! Renaming id first to avoid that ambiguity is working well 🙂