Write JSON string to JDBC as JSONB?

[John Tipper <jo...@hotmail.com>]

Hi all,

I am using PyFlink and SQL and have a JSON string that I wish to write to a JDBC sink (it's a Postgresql DB). I'd like to write that string to a column that is of type JSONB (or even JSON).

I'm getting exceptions when Flink tries to write the column:

Batch entry 0 INSERT INTO my_table(my_jsonb_column) VALUES ('{ ... a valid JSON string...}') ...
...
Error: column "my_jsonb_column" is of type jsonb but expression is of type character varying
Hint: You will need to rewrite or cast the expression

My table sink in Flink is defined like this:

CREATE TABLE my_table(
      my_jsonb_column STRING
) WITH (
      'connector' = 'jdbc'
)

and in my actual SQL database the column my_jsonb_column is actually defined of type JSONB.

I can happily execute SQL statements myself like this without an error where I write a string to the JSONB column:

INSERT INTO my_table(my_jsonb_column) VALUES ('{ ... a valid JSON string...}')


Is there any way of having Flink write to a JSONB column or otherwise get that JSON string into a JSONB column?

Many thanks,

John

Re: Write JSON string to JDBC as JSONB?

[John Tipper <jo...@hotmail.com>]

There may well be a better way, but I've been able to achieve this by executing the following in the DB:


CREATE CAST(VARCHAR AS JSONB) WITH INOUT AS IMPLICIT;

I think you can also probably do

CREATE CAST(VARCHAR AS JSONB) WITHOUT FUNCTION AS IMPLICIT;

However, I am working in AWS and AWS does not give the owner of the database sufficient permissions to execute this.


________________________________
From: John Tipper <jo...@hotmail.com>
Sent: 24 July 2022 22:01
To: user@flink.apache.org <us...@flink.apache.org>
Subject: Write JSON string to JDBC as JSONB?

Hi all,

I am using PyFlink and SQL and have a JSON string that I wish to write to a JDBC sink (it's a Postgresql DB). I'd like to write that string to a column that is of type JSONB (or even JSON).

I'm getting exceptions when Flink tries to write the column:

Batch entry 0 INSERT INTO my_table(my_jsonb_column) VALUES ('{ ... a valid JSON string...}') ...
...
Error: column "my_jsonb_column" is of type jsonb but expression is of type character varying
Hint: You will need to rewrite or cast the expression

My table sink in Flink is defined like this:

CREATE TABLE my_table(
      my_jsonb_column STRING
) WITH (
      'connector' = 'jdbc'
)

and in my actual SQL database the column my_jsonb_column is actually defined of type JSONB.

I can happily execute SQL statements myself like this without an error where I write a string to the JSONB column:

INSERT INTO my_table(my_jsonb_column) VALUES ('{ ... a valid JSON string...}')


Is there any way of having Flink write to a JSONB column or otherwise get that JSON string into a JSONB column?

Many thanks,

John