# Not able to insert JSON from PostgreSQL to elasticsearch. Getting error - "Exception when executing JDBC query"

**URL:** <https://discuss.elastic.co/t/not-able-to-insert-json-from-postgresql-to-elasticsearch-getting-error-exception-when-executing-jdbc-query/163173>\
**Category:** Logstash\
**Created:** [January 7, 2019, 9:24am UTC](https://discuss.elastic.co/t/not-able-to-insert-json-from-postgresql-to-elasticsearch-getting-error-exception-when-executing-jdbc-query/163173 "2019-01-07T09:24:02Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![poonia](https://avatars.discourse-cdn.com/v4/letter/p/bbce88/32.png) [@poonia](https://discuss.elastic.co/u/poonia)\
**Post date:** [January 7, 2019, 9:24am UTC](https://discuss.elastic.co/t/not-able-to-insert-json-from-postgresql-to-elasticsearch-getting-error-exception-when-executing-jdbc-query/163173/1 "2019-01-07T09:24:02Z")

</div>

Hi All,

I am trying to migrate data from postgresql server to elasticsearch. The postgres data is in JSONB format. When I am starting the river, I am getting the below error.

[INFO][logstash.agent] Successfully started Logstash API endpoint {:port=\>9600}  
[2019-01-07T14:22:34,625][INFO][logstash.inputs.jdbc] (0.128981s) SELECT to\_json(details) from inventory.retailer\_products1 limit 1  
[2019-01-07T14:22:35,099][WARN][logstash.inputs.jdbc] Exception when executing JDBC query {:exception=\>#\<Sequel::DatabaseError: Java::OrgLogstash::MissingConverterException: Missing Converter handling for full class name=org.postgresql.util.PGobject, simple name=PGobject\>}  
[2019-01-07T14:22:36,568][INFO][logstash.pipeline] Pipeline has terminated {:pipeline\_id=\>"main", :thread=\>"#\<Thread:0x6067806f run\>"}

I think the logstash is not able to identify the JSON data type.  
Below is my logstash conf file

input {  
jdbc {  
jdbc\_connection\_string =\> "jdbc:postgresql://localhost:5432/mydb"  
jdbc\_user =\> "postgres"  
jdbc\_password =\> "password"  
jdbc\_validate\_connection =\> true  
jdbc\_driver\_library =\> "/home/dell5/Downloads/postgresql-9.4.1208.jar"  
jdbc\_driver\_class =\> "org.postgresql.Driver"  
statement =\> "SELECT to\_json(details) from inventory.retailer\_products1 limit 1"  
}  
}

filter{  
json{  
source =\> "to\_json"  
}  
}

output {  
elasticsearch {  
index =\> "products-retailer"  
document\_type =\> "mapping-retailer"  
hosts =\> "localhost"  
}  
stdout{}  
}

The mapping I have defined for this is as below  
{  
"products-retailer": {  
"mappings": {  
"mapping-retailer": {  
"dynamic": "false",  
"properties": {  
"category": {  
"type": "keyword"  
},  
"id": {  
"type": "keyword"  
},  
"products": {  
"type": "nested",  
"properties": {  
"barcode": {  
"type": "text"  
},  
"batchno": {  
"type": "text"  
},  
"desc": {  
"type": "text"  
},  
"expirydate": {  
"type": "date",  
"format": "YYYY-MM-DD"  
},  
"imageurl": {  
"type": "text"  
},  
"manufaturedate": {  
"type": "date",  
"format": "YYYY-MM-DD"  
},  
"mrp": {  
"type": "text"  
},  
"name": {  
"type": "text",  
"fields": {  
"ngrams": {  
"type": "text",  
"analyzer": "autocomplete"  
}  
}  
},  
"openingstock": {  
"type": "text"  
},  
"price": {  
"type": "text"  
},  
"purchaseprice": {  
"type": "text"  
},  
"sku": {  
"type": "text"  
},  
"unit": {  
"type": "text"  
}  
}  
},  
"retailerid": {  
"type": "keyword"  
},  
"subcategory": {  
"type": "keyword"  
}  
}  
}  
}  
}  
}

The sample data in postgres column is below. It has nested json that I have defined in the mapping of elasticsearch.

{  
"id": "",  
"Category": "Bread and Biscuits",  
"products": {  
"MRP": "45",  
"SKU": "BREAD-1",  
"Desc": "Brown Bread",  
"Name": "Brown Bread",  
"Unit": "Packets",  
"Brand": "Britannia",  
"Price": "40",  
"BarCode": "1234567890",  
"BatchNo": "456789",  
"ImageUrl": "buscuits.jpeg",  
"ExpiryDate": "2019-06-01",  
"OpeningStock": "56789",  
"PurchasePrice": "30",  
"ManufactureDate": "2018-11-01"  
},  
"RetailerId": "1",  
"SubCategory": "Bread"  
}

Please suggest what am I missing here and if this is the right way to do it.

---

<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:** [February 4, 2019, 9:24am UTC](https://discuss.elastic.co/t/not-able-to-insert-json-from-postgresql-to-elasticsearch-getting-error-exception-when-executing-jdbc-query/163173/2 "2019-02-04T09:24:06Z")

</div>

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