Search code examples
mysqlpostgresqltalend

Talend Casting of JSON string to JSON or JSONB in PostgreSQL

I'm trying to use Talend to get JSON data that is stored in MySQL as a VARCHAR datatype and export it into PostgreSQL 9.4 table of the following type:

CREATE TABLE myTable( myJSON as JSONB)

When I try running the job I get the following error:

ERROR: column "json_string" is of type json but expression is of type character varying

Hint: You will need to rewrite or cast the expression. Position: 54

If I use python or just plain SQL with PostgreSQL insert I can insert a string such as '{"Name":"blah"}' and it understands it. 

INSERT INTO myTable(myJSON) VALUES ('{"Name":"blah"}');

Any Idea's how this can be done in Talend?

Solution

  • You can add a type-cast by opening the "Advanced Settings" tab on you "tPostgresqlOutput" component. Consider the following example:

    In this case, the input row to "tPostgresqlOutput_1" has one column data. This column is of type String and is mapped to the database column data of type VARCHAR (as by the default suggested by Talend):

    tPostgreslOutput_1 schema

    Next, open the component settings for tPostgresqlOutput_1 and locate the "Advanced settings" tab:

    Advanced Settings Tab

    On this tab, you can replace the existing data column by a new expression:

    • In the name column, specify the target column name.
    • In the SQL Expression column, do your type casting. In this case: "?::json"`. Note the usage of the placeholder character?`` which will be replaced with the original value.
    • In Position, specify Replace. This will replace the value proposed by Talend with your SQL expression (including the type cast).
    • As Reference Column use the source value.

    Type Casting

    This should do the trick.