Insert JSON value into Postgresql database table

[Tokbanov]

I’m facing a problem with insertion of JSON data into PostgreSQL database table column.
How to congifure “DB Insert” node in order to be able to insert JSON into a table with JSON type column?

[ipazin]

Hi there @Tokbanov,

what error are you seeing (if any)? I suppose both KNIME column type and db column type are JSON?

Br,
Ivan

[tobias.koetter]

Hello Tokbanov,
in order to write a KNIME JSON cell into a Postgres JSON column you need to do the following:
In the PostgreSQL Connector node you need to add the speciy the JDBC Parameter

stringtype=unspecified

and you need to add the following mapping

JSON->String->Varchar

to the Output Type Mapping tab

Once this is done you should be able to write JSON cells into JSON columns in the database.

When you want to read the data back into KNIME you can use the DB Type Mapper node to specify the mapping rule for each individuell JSON column (in this example called JSON) as shown below

You can find an example workflow on the KNIME Hub

Working with JSON columns in PostgreSQL – tobias.koetter

Bye
Tobias