# Load json text in oracle table to elastic search via logstash

**URL:** <https://discuss.elastic.co/t/load-json-text-in-oracle-table-to-elastic-search-via-logstash/367208>\
**Category:** Logstash\
**Tags:** elastic-stack-sql\
**Created:** [September 27, 2024, 2:16am UTC](https://discuss.elastic.co/t/load-json-text-in-oracle-table-to-elastic-search-via-logstash/367208 "2024-09-27T02:16:17Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![karthikeyanc2003](https://avatars.discourse-cdn.com/v4/letter/k/ea5d25/32.png) [@karthikeyanc2003](https://discuss.elastic.co/u/karthikeyanc2003)\
**Post date:** [September 27, 2024, 2:16am UTC](https://discuss.elastic.co/t/load-json-text-in-oracle-table-to-elastic-search-via-logstash/367208/1 "2024-09-27T02:16:17Z")

</div>

Dear Team,  
I have data in Oracle table Tbl\_user in the below table format. JSON column has the value in the JSON format. I want to insert this data into the Elastic search index with userid as a document ID via logstash. Request your support on , how to achive the same.

| User id | Json |
| --- | --- |
| 1001 | {user: Adam, age: 30, city: New York} |
| 1002 | {user: Eve, age: 25, city: Paris} |
| 1003 | {user: John, age: 20, city: Sydney} |

Thanks in advance  
Karthik

---

<div class="post-metadata">

**Author:** ![ashishtiwari1993](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ashishtiwari1993/32/135241_2.png) [@ashishtiwari1993](https://discuss.elastic.co/u/ashishtiwari1993)\
**Post date:** [September 27, 2024, 8:32am UTC](https://discuss.elastic.co/t/load-json-text-in-oracle-table-to-elastic-search-via-logstash/367208/2 "2024-09-27T08:32:42Z")

</div>

Hi Karthik,

Here is the quick guide how you can achieve this -

1. You will have to use JDBC [logstash input plugin](https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html) to get data from Oracle table.
2. Use [Elasticsearch output plugin](https://www.elastic.co/guide/en/logstash/current/plugins-outputs-elasticsearch.html) to push data into Elasticsearch.
3. For data processing like assigning user id to `_id` you need to use mutate in the filter.

Below are the sample file which you can give a try by adding some changes -

oracle\_to\_es.conf

```auto
input {
  jdbc {
    jdbc_connection_string => "jdbc:oracle:thin:@//your_oracle_host:1521/your_oracle_db"
    jdbc_user => "your_oracle_username"
    jdbc_password => "your_oracle_password"
    jdbc_driver_library => "/path/to/ojdbc8.jar"
    jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
    statement => "SELECT userid, json FROM Tbl_user"
  }
}

filter {
  json {
    source => "json"
    target => "parsed_json"
  }
  mutate {
    rename => { "userid" => "[@metadata][_id]" }
  }
}

output {
  elasticsearch {
    hosts => ["http://your_elasticsearch_host:9200"]
    index => "your_index_name"
    document_id => "%{[@metadata][_id]}"
    user => "elastic"
    password => "your_elastic_password"
  }
  stdout {
    codec => rubydebug
  }
}

```

Run

```auto
logstash -f oracle_to_es.conf

```

---

<div class="post-metadata">

**Author:** ![karthikeyanc2003](https://avatars.discourse-cdn.com/v4/letter/k/ea5d25/32.png) [@karthikeyanc2003](https://discuss.elastic.co/u/karthikeyanc2003)\
**Post date:** [September 27, 2024, 1:42pm UTC](https://discuss.elastic.co/t/load-json-text-in-oracle-table-to-elastic-search-via-logstash/367208/3 "2024-09-27T13:42:25Z")

</div>

Thanks Ashish , I will try it and let you know . Thanks for your support

---

<div class="post-metadata">

**Author:** ![karthikeyanc2003](https://avatars.discourse-cdn.com/v4/letter/k/ea5d25/32.png) [@karthikeyanc2003](https://discuss.elastic.co/u/karthikeyanc2003)\
**Post date:** [September 27, 2024, 7:47pm UTC](https://discuss.elastic.co/t/load-json-text-in-oracle-table-to-elastic-search-via-logstash/367208/4 "2024-09-27T19:47:12Z")

</div>

> [@ashishtiwari1993](#):
>
> `statement => "SELECT userid, json FROM Tbl_user"`

Dear Ashish,  
Thank you a lot for your support. Your solution works to import data. However, I forgot to say my requirement clearly. I already have an index with the mentioned field and want to map input JSON into the appropriate field. Please find the existing structure/data and newly inserted record from logstash.  
Is there any way to make json fields to index fields rather than getting it as "parsed\_json" or "json\_content" . Also want to know is it possible in nested table as well.

**-- structure**  
{  
"my\_index": {  
"aliases": {},  
"mappings": {  
"properties": {  
"@timestamp": {  
"type": "date"  
},  
"@version": {  
"type": "text",  
"fields": {  
"keyword": {  
"type": "keyword",  
"ignore\_above": 256  
}  
}  
},  
"age": {  
"type": "text",  
"fields": {  
"keyword": {  
"type": "keyword",  
"ignore\_above": 256  
}  
}  
},  
"city": {  
"type": "text",  
"fields": {  
"keyword": {  
"type": "keyword",  
"ignore\_above": 256  
}  
}  
},  
"user1": {  
"type": "text",  
"fields": {  
"keyword": {  
"type": "keyword",  
"ignore\_above": 256  
}  
}  
},  
"userid": {  
"type": "text",  
"fields": {  
"keyword": {  
"type": "keyword",  
"ignore\_above": 256  
}  
}  
}  
}  
}  
}  
}

**-- data**  
"hits": [  
{  
"\_index": "my\_index",  
"\_id": "6iXxNJIBnBMzydZlUOYX",  
"\_score": 1.0,  
"\_source": {  
"userid": "1001",  
"user1": "sam",  
"city": "tornto",  
"@timestamp": "2024-09-27T19:24:42.590670979Z",  
"@version": "1",  
"age": "32"  
}  
},

**-- newly inserted data via logstash json**  
{  
"\_index": "my\_index",  
"\_id": "1005",  
"\_score": 1.0,  
"\_source": {  
"parsed\_json": {  
"user": "Eve",  
"city": "Paris",  
"age": "25"  
},  
"json\_content": "{"user": "Eve", "age": "25", "city": "Paris"}",  
"@version": "1",  
"@timestamp": "2024-09-27T19:36:13.840690324Z"  
}  
}

thanks in advance once again.

---

<div class="post-metadata">

**Author:** ![karthikeyanc2003](https://avatars.discourse-cdn.com/v4/letter/k/ea5d25/32.png) [@karthikeyanc2003](https://discuss.elastic.co/u/karthikeyanc2003)\
**Post date:** [October 2, 2024, 5:38pm UTC](https://discuss.elastic.co/t/load-json-text-in-oracle-table-to-elastic-search-via-logstash/367208/5 "2024-10-02T17:38:44Z")

</div>

@ashishtiwari1993 : Can you please help with the above scenario?

---

<div class="post-metadata">

**Author:** ![chouben](https://avatars.discourse-cdn.com/v4/letter/c/e495f1/32.png) [@chouben](https://discuss.elastic.co/u/chouben)\
**Post date:** [October 4, 2024, 6:41am UTC](https://discuss.elastic.co/t/load-json-text-in-oracle-table-to-elastic-search-via-logstash/367208/6 "2024-10-04T06:41:06Z")

</div>

You are instructing Logstash to parse this data:

> **[JSON filter plugin | Logstash Reference \[8.15\] | Elastic](https://www.elastic.co/guide/en/logstash/current/plugins-filters-json.html)**

> [@ashishtiwari1993](#):
>
> ```auto
> filter {
> json {
> source => "json"
> target => "parsed_json"
> }
> }
> 
> ```

If you don't want parsing, don't add the filter 🙂

---

<div class="post-metadata">

**Author:** ![karthikeyanc2003](https://avatars.discourse-cdn.com/v4/letter/k/ea5d25/32.png) [@karthikeyanc2003](https://discuss.elastic.co/u/karthikeyanc2003)\
**Post date:** [October 4, 2024, 8:30pm UTC](https://discuss.elastic.co/t/load-json-text-in-oracle-table-to-elastic-search-via-logstash/367208/7 "2024-10-04T20:30:53Z")

</div>

> [@ashishtiwari1993](#):
>
> ```auto
> mutate {
> rename => { "userid" => "[@metadata][_id]" }
> }
> 
> ```

Thanks a lot for your support, it works for me

---

<div class="post-metadata">

**Author:** ![ashishtiwari1993](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ashishtiwari1993/32/135241_2.png) [@ashishtiwari1993](https://discuss.elastic.co/u/ashishtiwari1993)\
**Post date:** [October 5, 2024, 4:32am UTC](https://discuss.elastic.co/t/load-json-text-in-oracle-table-to-elastic-search-via-logstash/367208/8 "2024-10-05T04:32:16Z")

</div>

Welcome karthikeyan 🙂