topic Re: How to write String to Postgres as JSONB? in Talend Studio

How to write String to Postgres as JSONB?

Nesuark — Mon, 14 Apr 2025 12:14:06 GMT

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.

Re: How to write String to Postgres as JSONB?

quentin-vigne — Mon, 14 Apr 2025 12:18:59 GMT

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

Re: How to write String to Postgres as JSONB?

Nesuark — Mon, 14 Apr 2025 12:42:36 GMT

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

Re: How to write String to Postgres as JSONB?

quentin-vigne — Mon, 14 Apr 2025 13:09:23 GMT

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

- Quentin

Re: How to write String to Postgres as JSONB?

Nesuark — Mon, 14 Apr 2025 13:23:54 GMT

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

Re: How to write String to Postgres as JSONB?

quentin-vigne — Mon, 14 Apr 2025 14:50:49 GMT

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

Re: How to write String to Postgres as JSONB?

Nesuark — Tue, 15 Apr 2025 07:22:40 GMT

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.

Re: How to write String to Postgres as JSONB?

quentin-vigne — Tue, 15 Apr 2025 07:41:37 GMT

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