How to write String to Postgres as JSONB?

[Nesuark]

Hallo,

I try to load  data via the tJiraInput to my Postgres DB. But the data is not loading to Postgres, what am i missing?

I created the table in Postgres with:

CREATE TABLE talend_jira.jira_issues_raw (

json JSONB

);

and in Talend i try to CAST the string to json, but i can't get any data into my Database.

[quentin-vigne]

Hi @Nesuark 

I recently helped someone with the same issue here : https://community.qlik.com/t5/Talend-Studio/insert-JSONB-in-Postgresql/m-p/2508375#M141825

I'll copy and paste it here in case the other topic get deleted : 

--

You need to start setting up the desired column in Talend to "string"

Then in your tDBOutput go to Advanced Settings

And in Additional Column add a new value with the same name as your output column :

in SQL Expression add : 

"?::json"

This help convert your string to json. We use "?" because it's a pattern matching character. 

In Position select Replace and in Reference column select the output column.

If we say that your json string is "abcdef" then Talend will output your data it will look like this : 

INSERT INTO table VALUES ("abcdef"::json)

- Quentin

[Nesuark]

Thanks for the reply, but i already saw your post.
Thats why I am not sure what i am missing here.

[quentin-vigne]

Can you screenshot your tDBOutput setup to make sure everything is setup correctly ?

- Quentin

[Nesuark]

Of course, let me know if you need more Information.

[quentin-vigne]

Does your tDBOutput schema look like this ? 

"json" column as string to "json" output column as string ? 

Also, try to use the option "Create table if not exists" in tDBOutput and drop your table to see if it work (it might create it as a string column) -> This way we can see if the component is the problem or if it's more database related.

And maybe try something else instead of "?::json" do "?::jsonb"

If there is an error message can you share it ? Try to activate log-level info to get more from Talend 

- Quentin

[Nesuark · Accepted solution]

I could fix the Problem: 

The database transaction required a commit, because the changes were never actually made before.
So setting a tDBCommit at the end solved it. The transformation to jsonb works just as you described it.

Thank you for your support in finding the problem.

[quentin-vigne]

You can add a tDBCommit or you can also check the "Auto commit" inside advanced parameters of the tDBConnection if you don't want to have to commit everytime you use a connection.

- Quentin