How to fetch number from the NVARCHAR value depending on the condition in Azure data Factory derived column?

Question

How to fetch number from the NVARCHAR value depending on the condition in Azure data Factory derived column?

Aravind 20

Hi EveryOne,

We are migrating data from azure sql server to azure postgresql. so we have a requirement like in sql server one table have metadata column datatype: nvarchar

Example data: { "Type": 5, "TypeId": 50328 }

so there is type:5 then typeid value need to fetch in derived column so i will map this to postgresql int column in sink mapping but Need an expression in derived column. I tried few things but not working. like

IIF(BYPOSITION('"Type": 5' IN metadata) > 0,
    TOINTEGER(SUBSTRING(metadata, BYPOSITION('"TypeId":' IN metadata) + 10, BYPOSITION(',' IN SUBSTRING(metadata, BYPOSITION('"TypeId":' IN metadata) + 10)) - 1)),
    NULL())

no functions working for this in the derived column so please give the correct expression for this requirement in derived column in ADF.

Thanks & Regards

Aravind

AnnuKumari-MSFT 34,566 Reputation points Microsoft Employee Moderator

2023-12-15T07:14:38.99+00:00

Hi Aravind ,

Welcome to Microsoft Q&A platform and thanks for posting your query here.

As per my understanding you are trying to fetch out the typeid out of a json when type value is 5 and map it to int column in postGre sql sink. Please correct me if my understanding is wrong.

You can rather use substringIndex function which extracts the substring before count occurrences of the delimiter.

Example: substringIndex('111-222-333', '-', 1) -> '111'

Other way would be to convert the json value into tabular format by flattening the column. Type and TypeId will be appearing as two different columns in tabular structure after flattening.

Thereafter, you can use filter transformation to fetch out the typeId values for all the rows where type equals 5.

Hope it helps. Please let me know in case it was helpful. Thanks
Aravind 20 Reputation points

2023-12-15T11:55:52.0766667+00:00

Hi AnnuKumari-MSFT,

Thanks for a prompt response, but substringIndex('111-222-333', '-', 1) -> '111' not working for my requirement. this is example data { "Type": 5, "TypeId": 50328 } so already tried this substringindex() not working and we can't able to split the column in a table, that's a production server. we only perform the things in ADF level only.

so is any other solution for this in ADF level?

Thanks & Regards

Aravind

Your answer

AnnuKumari-MSFT 34,566 Reputation points Microsoft Employee Moderator

2023-12-15T07:14:38.99+00:00

Hi Aravind ,

Welcome to Microsoft Q&A platform and thanks for posting your query here.

As per my understanding you are trying to fetch out the typeid out of a json when type value is 5 and map it to int column in postGre sql sink. Please correct me if my understanding is wrong.

You can rather use substringIndex function which extracts the substring before count occurrences of the delimiter.

Example: substringIndex('111-222-333', '-', 1) -> '111'

Other way would be to convert the json value into tabular format by flattening the column. Type and TypeId will be appearing as two different columns in tabular structure after flattening.

Thereafter, you can use filter transformation to fetch out the typeId values for all the rows where type equals 5.

Hope it helps. Please let me know in case it was helpful. Thanks
Aravind 20 Reputation points

2023-12-15T11:55:52.0766667+00:00

Hi AnnuKumari-MSFT,

Thanks for a prompt response, but substringIndex('111-222-333', '-', 1) -> '111' not working for my requirement. this is example data { "Type": 5, "TypeId": 50328 } so already tried this substringindex() not working and we can't able to split the column in a table, that's a production server. we only perform the things in ADF level only.

so is any other solution for this in ADF level?

Thanks & Regards

Aravind

Share via

How to fetch number from the NVARCHAR value depending on the condition in Azure data Factory derived column?

Your answer