Name is required.
Email address is required.
Invalid email address
Answer is required.
Exceeding max length of 5KB

JSON straucture

Hi Matillion Team,

I am developing ELT process in which one of the source column is varchar thats need to be converted to Variant data type. I am able to achieve it using Convert Type component in transformation job. But when I looked at the data for variant column it adds " at the beginning and at the end. Also it add \ for each key in the json -
Below value of variant column for your reference -
"{\"is_source\": true, \"connect_pair_id\": 247623, \"connect_entity_types\": [19]}"

Technically, it should look like -
{"is_source": true, "connect_pair_id": 247623, "connect_entity_types": [19]}

Could you help me on this?

Also, I dont understand the concept of recreating table using Database Query component. I am creating table manually but each time when I run the process it simply recreates the table as same structure of source table.

Let me know.

Thanks,
Amit

1 Community Answers

Matillion Agent  

Paul Johnson —

Hi Amit, this doesn’t sound like the convert type component is working correctly – I will see if I can reproduce the error and get it fixed.

However there is an alternative way to achieve what you want.
1. First use a Create Table component and set the column type to variant.
2. On the load component, go to load options and set “recreate target table” to “Off”

It sounds like you are doing step 1 but not step 2.

The reason the behaviour is on by default is that in most DW scenarios, staging tables are transient and do not store any historical data so they get recreated each time. If this is your desired workflow, simply use a truncate table component before the load and the table will be emptied but not dropped.

Regards,
Paul

Post Your Community Answer

To add an answer please login