Search code examples
jsonpostgresqlpentahokettlejsonb

Pentaho Kettle - From JSON Table Output to Postgresql JSONB field


How do I get Pentaho Kettle send JSON data to a PostgreSQL JSONB field? Anytime I try to output JSON data to a Postgresql JSONB field type I get an error. Sending JSON data to PostgreSQL text fields works fine, but I want to maintain the JSON format in PostgreSQL.

Posted this question in the Kettle forum, but did not get a response. So I'm trying this forum.

Thanks for your input!

Edit: When I run the Pentaho Kettle transformation and the target field in Postgresql is of type JSONB, I get this error:

2015/10/29 15:39:32 - Table output 2.0 - Connected to database [test] (commit=1000)
2015/10/29 15:40:43 - Table output 2.0 - ERROR (version 5.4.0.1-130, build 1 from 2015-06-14_12-34-55 by buildguy) : Unexpected batch update error committing the database connection.
2015/10/29 15:40:43 - Table output 2.0 - ERROR (version 5.4.0.1-130, build 1 from 2015-06-14_12-34-55 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseBatchException: 
2015/10/29 15:40:43 - Table output 2.0 - Error updating batch
2015/10/29 15:40:43 - Table output 2.0 - Batch entry 0 INSERT INTO test_raw_dataj (sensor_readings) VALUES ( '{"data":[{"readings":"[[1325404802000,2.8020381927490234],[1325404812000,2.8020381927490234],[1325404822000,2.817678451538086],[1325404832000,2.817678451538086],[1325404842000,2.8333663940429688],[1325404852000,2.8333663940429688]]","id":"59997f"}]}') was aborted.  Call getNextException to see the cause.
2015/10/29 15:40:43 - Table output 2.0 -    at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2743)
2015/10/29 15:40:43 - Table output 2.0 -    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1928)
2015/10/29 15:40:43 - Table output 2.0 -    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:405)
2015/10/29 15:40:43 - Table output 2.0 -    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2892)
2015/10/29 15:40:43 - Table output 2.0 -    at org.pentaho.di.core.database.Database.emptyAndCommit(Database.java:1362)
2015/10/29 15:40:43 - Table output 2.0 -    ... 3 more
2015/10/29 15:40:43 - Table output 2.0 - Finished processing (I=0, O=0, R=1, W=0, U=0, E=1)

However, running the same transformation and the target field in Postgresql is of type TEXT, everything works fine.


Solution

  • I tried to recreate the issue and I found the solution at last.
    In your table output step

    • Go to Edit button in front of connection
    • Then select Options.
    • Give Parameter value as follows

    Parameter stringtype and value unspecified

    enter image description here

    NB: In General tab you must select your Connection type as PostgreSQL and fill the necessary settings.