How to combine two JSON nodes in ADF

Question

How to combine two JSON nodes in ADF

StevenT 0

i want to combine two json nodes (in bold below) into one complex node, using azure data factory, i am using the data flow component but struggling to transform it in regards to the below example (any help would be greatly appreciated) e.g. { "op": "add", "path": "/products/AN103", "value": { "attributes": { "product_type": "Physical", "price": 12.34 } } } into a new node 'priceInfo' e.g. priceInfo { "product_type": "Physical", "price": 12.34}

Vahid Ghafarpour 23,385 Reputation points Volunteer Moderator

2024-02-21T16:35:01.83+00:00
Thanks for posting your question in the Microsoft Q&A forum.

You can use the Derived Column transformation with following expression:

iif(path == "/products/AN103", namedStruct('product_type', value.attributes.product_type, 'price', value.attributes.price), null)

And configure the Derived Column transformation to output priceInfo as a complex object. ** Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful **
StevenT 0 Reputation points

2024-02-21T16:46:17+00:00

hi, thanks for the quick response, but when i use the expression provided, its complaining that it doesnt know what 'namedStruct' is - im using v2 data factory and cant find any function that looks that like
StevenT 0 Reputation points

2024-02-22T13:40:13.2233333+00:00

BhargavaGunnam-MSFT - this is perfect, thanks very much
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2024-02-23T18:05:54.4666667+00:00

Hello StevenT ,

If you don't have any further questions, please consider accepting the answer by hitting the Accept answer as it helps the community look for answers to similar questions.
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2024-02-26T16:29:36.7066667+00:00

Hello StevenT ,

If you don't have any further questions, please consider accepting the answer by hitting the Accept answer as it helps the community look for answers to similar questions.

1 answer

Your answer

Vahid Ghafarpour 23,385 Reputation points Volunteer Moderator

2024-02-21T16:35:01.83+00:00

Thanks for posting your question in the Microsoft Q&A forum.

You can use the Derived Column transformation with following expression:

iif(path == "/products/AN103", namedStruct('product_type', value.attributes.product_type, 'price', value.attributes.price), null)

And configure the Derived Column transformation to output priceInfo as a complex object. ** Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful **
StevenT 0 Reputation points

2024-02-21T16:46:17+00:00

hi, thanks for the quick response, but when i use the expression provided, its complaining that it doesnt know what 'namedStruct' is - im using v2 data factory and cant find any function that looks that like
StevenT 0 Reputation points

2024-02-22T13:40:13.2233333+00:00

BhargavaGunnam-MSFT - this is perfect, thanks very much
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2024-02-23T18:05:54.4666667+00:00

Hello StevenT ,

If you don't have any further questions, please consider accepting the answer by hitting the Accept answer as it helps the community look for answers to similar questions.
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2024-02-26T16:29:36.7066667+00:00

Hello StevenT ,

If you don't have any further questions, please consider accepting the answer by hitting the Accept answer as it helps the community look for answers to similar questions.

Answer 1

Bhargava-MSFT 31,261 Microsoft Employee Moderator

Hello StevenT,

Can you please try the below:

I have used two derived columns to extract the relevant fields

In the 1st derived column: Create a new column: producttype and set the expression to: value.attributes.product_type price to: value.attributes.price

on the second derived column: create a new colum: priceinfo and set the expression to:

iif(isNull(price), '{"producttype": "' + producttype + '", "price": null}', '{"producttype": "' + producttype + '", "price": ' + toString(price) + '}')

enter image description here

I hope this helps.

Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2024-02-22T14:54:49.95+00:00

StevenT ,

I am glad the answer was helpful. If you don't have any further questions, please consider accepting the answer by hitting the Accept answer and** up-vote **as it helps the community look for answers to similar questions.

Share via

How to combine two JSON nodes in ADF

1 answer

Your answer