How to use a CAST expression in a data transfer table mapping column #23472
Replies: 8 comments
-
@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. |
Beta Was this translation helpful? Give feedback.
-
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: |
Beta Was this translation helpful? Give feedback.
-
Hello @LawrenceLau2020 Unfortunately, our expressions are not as smart as you think. |
Beta Was this translation helpful? Give feedback.
-
Thanks for the link, @LonwoLonwo , I tried using the syntax: "content.json(mycolumnFromQuery)" within the transformer expression but I'm getting this error now: 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? |
Beta Was this translation helpful? Give feedback.
-
@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? |
Beta Was this translation helpful? Give feedback.
-
@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. |
Beta Was this translation helpful? Give feedback.
-
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: 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? |
Beta Was this translation helpful? Give feedback.
-
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.
Beta Was this translation helpful? Give feedback.
All reactions