# Getting internal "\_id" field via jdbc

**URL:** <https://discuss.elastic.co/t/getting-internal-id-field-via-jdbc/355314>\
**Category:** Elasticsearch\
**Tags:** elastic-stack-sql\
**Created:** [March 13, 2024, 2:06pm UTC](https://discuss.elastic.co/t/getting-internal-id-field-via-jdbc/355314 "2024-03-13T14:06:11Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Kenjiro](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kenjiro/32/121861_2.png) [@Kenjiro](https://discuss.elastic.co/u/Kenjiro)\
**Post date:** [March 13, 2024, 2:06pm UTC](https://discuss.elastic.co/t/getting-internal-id-field-via-jdbc/355314/1 "2024-03-13T14:06:11Z")

</div>

Hello,

I'm new to elasticsearch and as far as I've read, \_id field is not accesible via sql endpoint at the moment.

I've read some posts about duplicating it as a regular field in order to reach via sql endpoint. But I cannot find a document about how to actually do it.

Can you please advise?

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [March 13, 2024, 3:30pm UTC](https://discuss.elastic.co/t/getting-internal-id-field-via-jdbc/355314/2 "2024-03-13T15:30:04Z")

</div>

Welcome!

Easiest thing is to do that from the client which is sending data to Elasticsearch:

```auto
PUT /index/_doc/1
{
  "id": "1",
  "foo": "bar"
}

```

If this does not work for you, there are many other options, like using [runtime fields](https://www.elastic.co/guide/en/elasticsearch/reference/current/runtime.html) or using an ingest pipeline to [set this value](https://www.elastic.co/guide/en/elasticsearch/reference/current/set-processor.html) automatically at index time... Something like:

```auto
PUT _ingest/pipeline/set-id
{
  "processors": [
    {
      "set": {
        "field": "id",
        "value": "{{{_id}}}"
      }
    }
  ]
}

```

And then call:

```auto
PUT /index/_doc/1?pipeline=set-id
{
  "foo": "bar"
}

```

Or define the pipeline as [a default one](https://www.elastic.co/guide/en/elasticsearch/reference/current/ingest.html#set-default-pipeline).

As you can see, many options. So please clarify your exact need and architecture so we can give even better answers 😉

BTW I did not check initially but where dii you see that you can't access the `_id` field using the SQL layer?

---

<div class="post-metadata">

**Author:** ![Kenjiro](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kenjiro/32/121861_2.png) [@Kenjiro](https://discuss.elastic.co/u/Kenjiro)\
**Post date:** [March 13, 2024, 5:28pm UTC](https://discuss.elastic.co/t/getting-internal-id-field-via-jdbc/355314/3 "2024-03-13T17:28:52Z")

</div>

Hello,

> [@dadoonet](#):
>
> BTW I did not check initially but where dii you see that you can't access the `_id` field using the SQL layer?

This is stated here, on several posts from elastic team members. One example being --\> [Is there a way to access internal \_id and \_type fields in ES SQL query (XPack)?](https://discuss.elastic.co/t/is-there-a-way-to-access-internal-id-and-type-fields-in-es-sql-query-xpack/174894)

> [@dadoonet](#):
>
> As you can see, many options. So please clarify your exact need and architecture so we can give even better answers 😉

I need to extract data from elasticsearch into a relational database. Since the data contains no logical unique keys (even not a composite one), I have to rely elastic \_id field to ensure data consistency. But I cannot reach the field via elasticsearch JDBC driver.

Any suggestions?

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [March 13, 2024, 7:17pm UTC](https://discuss.elastic.co/t/getting-internal-id-field-via-jdbc/355314/4 "2024-03-13T19:17:42Z")

</div>

> [@Kenjiro](#):
>
> This is stated here, on several posts from elastic team members. One example being --\> [Is there a way to access internal \_id and \_type fields in ES SQL query (XPack)?](https://discuss.elastic.co/t/is-there-a-way-to-access-internal-id-and-type-fields-in-es-sql-query-xpack/174894)

It's from 2019. 5 years ago. Did you try it? I meant that the post was about filtering by id not getting back the id.

> [@Kenjiro](#):
>
> Any suggestions?

I think I proposed some options already.

---

<div class="post-metadata">

**Author:** ![Kenjiro](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kenjiro/32/121861_2.png) [@Kenjiro](https://discuss.elastic.co/u/Kenjiro)\
**Post date:** [March 13, 2024, 7:34pm UTC](https://discuss.elastic.co/t/getting-internal-id-field-via-jdbc/355314/5 "2024-03-13T19:34:50Z")

</div>

> [@dadoonet](#):
>
> It's from 2019. 5 years ago. Did you try it? I meant that the post was about filtering by id not getting back the id.

Well, of course I tried 🙂

As far as I understand, it's easy for the team to make \_id field accesible via sql endpoint. But things get complicated when it comes to other sql functionality like aggregations, grouping etc. Therefore they chose not to include that functionality altogether.

Several requests have been made over the years but all have been dismissed. Some directly by denial, some indirectly via redirecting to other requests and have them closed due to inactivity.

> [@dadoonet](#):
>
> I think I proposed some options already.

I'll forward those to our elastic admin to see if we can do it.

Thank you 🙂

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [April 10, 2024, 7:34pm UTC](https://discuss.elastic.co/t/getting-internal-id-field-via-jdbc/355314/6 "2024-04-10T19:34:56Z")

</div>

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