How to use a CAST expression in a data transfer table mapping column

[LawrenceLau2020]

Hi there, I'm extracting data from SQL Server into Postgres and I have a column in SQL Server stored as a string which I'm converting to JSON before inserting into a JSONB column in Postgres:

JSON_QUERY(mytablecolumn)

In my database task -> Tables Mapping -> for that column, I've added a transformation expression:

CAST(mytablecolumn AS jsonb)

but I'm getting this error:
org.apache.commons.jexl3.JexlException$Parsing: org.jkiss.dbeaver.model.virtual.DBVUtils.parseExpression@1:27 parsing error in 'AS'

Do I have the wrong syntax, as I'm assuming there is a way to CAST what I've converted into a JSONB data type before inserting into Postgres?

Thanks in Advance.

[LawrenceLau2020]

@LonwoLonwo I'm not sure if it's possible to CAST something in a transformation expression during table mapping? If not, is there a way we could add SQL scripts that can run in Postgres after the ETL database task completes?

Very much appreciated.

[LawrenceLau2020]

Since we are storing JSON objects as nvarchar(max) type in SQL Server, I've also tried creating an expression JSON.parse(mytablecolumn) and I get this error when trying to run our ETL db task from SQL Server to Postgres:
org.apache.commons.jexl3.JexlException$Parsing: org.jkiss.dbeaver.model.virtual.DBVUtils.parseExpression@1:11 parsing error in '(' at org.apache.commons.jexl3.JexlEngine.createExpression(JexlEngine.java:304)

[LonwoLonwo]

Hello @LawrenceLau2020

Unfortunately, our expressions are not as smart as you think.
And they are definitely not about SQL syntax.
All available options are described here: https://github.com/dbeaver/dbeaver/wiki/Virtual-column-expressions

[LawrenceLau2020]

Thanks for the link, @LonwoLonwo , I tried using the syntax: "content.json(mycolumnFromQuery)" within the transformer expression

but I'm getting this error now:
JexlException$Variable: org.jkiss.dbeaver.model.virtual.DBVUtils.parseExpression@1:1 undefined variable content

Btw, I'm using DBeaver Version 23.3.2.202401072024. I'm also using a custom query to our SQL Server database in order to generate the columns we are transferring over to Postgres. Based on the documentation you sent, it mentions something about virtual columns. Do we need to create a virtual column in our database task which does the ETL from SQL Server to Postgres?

[LawrenceLau2020]

@LonwoLonwo I also tried using a transformer expression like this: "mycolumnFromQuery.json(mycolumnFromQuery)" since the error in the previous comment might have something to do with "content.json" not being recognized in the expression.

but that gives me this error, doesn't seem to recognize the json function?
org.apache.commons.jexl3.JexlException$Method: org.jkiss.dbeaver.model.virtual.DBVUtils.parseExpression@1:21 unsolvable function/method 'json'

[LawrenceLau2020]

@LonwoLonwo If I created the "virtual column" through DBeaver on the source SQL Server database on the column which stores JSON data, how do I reference this new "virtual column" from my database ETL task. I tried adding the virtual column's name in the transformer expression, but I don't think that's the way.

[LawrenceLau2020]

Also, the value for this new "virtual column" in the data viewer doesn't look like JSON data to me:

The data in my SQL Server table is stored as type NVACHAR(MAX) and looks like this:
[{"xxxxxx":20,"yyyyyyy":0}]

I'm assuming I'm going to somehow need to map the JSON data from the source "virtual column" to the target JSONB column within the tables mapping section of the data transfer task?