CODEWITHSUNDEEP
mysqlpostgresqltalend
codeBarer
codeBarer

Reputation: 2378

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?

Upvotes: 3

Views: 4478

Answers (2)

Jai Prakash
Jai Prakash

Reputation: 2759

enter image description here

Here is a sample schema for where in i have the input row 'r' which has question_json and choice_json columns which are json strings. From which i know the key what i wanted to extract and here is how i do

enter image description here

you should look at the columns question_value and choice_value. Hope this helps you

Upvotes: 0

exhuma
exhuma

Reputation: 21697

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.

Upvotes: 7

Related Questions