title: Not able to insert JSON from PostgreSQL to elasticsearch. Getting error - “Exception when executing JDBC query”
body: <p>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.</p>
  
  <blockquote>
  <pre><code> [INFO ][logstash.agent ] Successfully started Logstash API endpoint {:port=&gt;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=&gt;#&lt;Sequel::DatabaseError: Java::OrgLogstash::MissingConverterException: Missing Converter handling for full class name=org.postgresql.util.PGobject, simple name=PGobject&gt;}
   [2019-01-07T14:22:36,568][INFO ][logstash.pipeline ] Pipeline has terminated {:pipeline_id=&gt;"main", :thread=&gt;"#&lt;Thread:0x6067806f run&gt;"}
  </code></pre>
  </blockquote>
  
  <p>I think the logstash is not able to identify the JSON data type.</p>
  
  <p>Below is my logstash conf file</p>
  
  <pre><code>    input {
          jdbc {
              jdbc_connection_string =&gt; "jdbc:postgresql://localhost:5432/mydb"
              jdbc_user =&gt; "postgres"
              jdbc_password =&gt; "password"
              jdbc_validate_connection =&gt; true
              jdbc_driver_library =&gt; "/home/dell5/Downloads/postgresql-9.4.1208.jar"
              jdbc_driver_class =&gt; "org.postgresql.Driver"
              statement =&gt; "SELECT to_json(details) from inventory.retailer_products1 limit 1"
          }
      }
  
  filter{
      json{
          source =&gt; "to_json"
      }
  }
  
  output {
      elasticsearch {
          index =&gt; "products-retailer"
          document_type =&gt; "mapping-retailer"
          hosts =&gt; "localhost"
          }
          stdout{}
      }
  </code></pre>
  
  <p>The mapping I have defined for this is as below</p>
  
  <pre><code>{
  "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"
                          }
                      }
                  }
              }
          }
      }
  </code></pre>
  
  <p>The sample data in postgres column is below. It has nested json that I have defined in the mapping of elasticsearch.</p>
  
  <pre><code>{
      "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"
  }
  </code></pre>
  
  <p>Please suggest what am I missing here and if this is the right way to do it.</p>
  
  <p>I am using Elasticsearch 6.5.1. PostgreSQL 9.5.</p>