Ask Your Question
1

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

2 Answers

Sort by ยป oldest newest most voted
0

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

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 https://streamsets.com/documentation/...

edit flag offensive delete link more

Comments

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
0

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 https://issues.streamsets.com/ and we can look at how we can add support.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

2 followers

Stats

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

Seen: 105 times

Last updated: Aug 22