Ask Your Question

Saving field to jsonb column in Postgres using JDBC Producer

asked 2018-08-13 10:15:56 -0500

vic_pleexy gravatar image

updated 2018-08-14 10:34:23 -0500

metadaddy gravatar image

I have a pipeline that moves data from MongoDB to Postgres database.

Majority of Mongo's fields are scalars and work fine. However, one field needs to be stored as JSON into jsonb or json column in Postgree table. I can't find a way to make it work. Here is what I tried:

  • left field untouched -> got a validation message saying that Map field can't be processed.
  • Added JSON generator, converted Map to string -> got a validation message that field of type string cannot be saved to a destination column
  • Tried explicit mapping, saying to use database column type -> same result as above

It seems that JDBC Producer is not "aware" of JSONB field type and can't validate that string can be saved there.

Is there any way to work around it without fixing it in code? For now I save the data to string column and than use database trigger to convert to jsonb, but it's an ugly solution.

edit retag flag offensive close merge delete

3 Answers

Sort by ยป oldest newest most voted

answered 2020-10-01 10:20:21 -0500

Our JDBC producer can absolutely write into PostgreSQL types JSON and JSONB. We're running internal tests ensuring so on a daily basis.

The problem here is in my mind a use case one - Data Collector will read Mongo DB document fully into our in-memory record format. That is why the error "Map field can't be processed" - the "nested" JSON in MongoDB is parsed into our Map data type (which is an example of a nested type). You should be able to trivially solve that by adding a Data Generator and converting the nested Field into a scalar JSON value stored in String field type that can be then subsequently inserted into PostgreSQL's JSON column.

edit flag offensive delete link more

answered 2018-08-22 19:44:46 -0500

metadaddy gravatar image

I don't think there is a way to do this right now as the JDBC Producer is not aware of any non-standard JDBC fields. The Postgres schema says 'JSONB', and the producer doesn't know that it can write a string to a column of that type. Please file an enhancement request at and we can look at how we can add support.

edit flag offensive delete link more

answered 2018-09-16 07:28:46 -0500

Philipp gravatar image

I haven't tried it myself, but I could imagine that it may help to explicitly cast the string value to jsonb in the "field to column mapping" of the JDBC producer. I.e. cast(? as jsonb)

See also

edit flag offensive delete link more


I just tried it, but without success. However, if anyone could explain _why_ it doesn't work, I'd be very interested...

Philipp gravatar imagePhilipp ( 2018-09-16 14:57:46 -0500 )edit
Login/Signup to Answer

Question Tools



Asked: 2018-08-13 10:14:23 -0500

Seen: 1,220 times

Last updated: Oct 01 '20