Error parsing json using jdbc input


(Jorge) #1

Hi everybody,

I am trying to parse a json in order to have a nested field in my docu but I get the following message:
Error parsing json {:source=>"catejories_json", :raw=>#Java::OrgPostgresqlUtil::PGobject:0x54047773, :exception=>java.lang.ClassCastException: org.jruby.java.proxies.ConcreteJavaProxy cannot be cast to org.jruby.RubyIO, :level=>:warn}

the data that I use is:
customer == > type string
catejories_json ==> type json

customer
1

catejories_json
[{"first_level":592,"second_level":[20521]},{"first_level":335,"second_level":null},{"first_level":380,"second_level":null},{"first_level":661,"second_level":null},{"first_level":391,"second_level":[662,20277]}]

the config file for logstash:
input {
jdbc {
jdbc_connection_string => "jdbc:postgresql://myhost:5432/mydb"
jdbc_user => "postgres"
jdbc_password => "mypasword"
jdbc_validate_connection => true
jdbc_driver_library => "/usr/share/elasticsearch/lib/postgresql-9.4.1208.jar"
jdbc_driver_class => "org.postgresql.Driver"
statement => "SELECT customer, catejories_json FROM customers WHERE customer = '1' "
jdbc_paging_enabled => "true"
jdbc_page_size => "50000"
}
}

filter {
json {
source => "catejories_json"
target => "categories_obj"
remove_field => ["catejories_json"]
}
}

output {
elasticsearch{
index => "test"
document_type => "type_test"
document_id => "customer"
}
}
and the mapping is:
POST test/
{
"mappings": {
"type_test": {
"properties": {
"customer": {
"type": "string"
},
"categories_obj": {
"type": "nested",
"properties": {
"first_level": {
"type": "integer"
},
"second_level": {
"type": "integer"
}
}
}
}
}
}
}

I have use http://jsonlint.com/ to test the json field and the result is successful.
I have install: codec-multiline and off course json filter.

In advance, thousands thanks for your support.

Regards,

Jorge von Rudno


#2

Cast your Resultfield to Text.
Like:
SELECT customer, catejories_json::text FROM customers WHERE customer = '1'

This works for me.


(system) #3