Transform a regular array into an array of objects inside a Mapping Data Flow in Azure Data Factory

Question

Transform a regular array into an array of objects inside a Mapping Data Flow in Azure Data Factory

Jose Martinez 0

In azure data factory, I have a column which is an arrayof strings like in this (image below):

costGroup = ["GH", "APT"]

User's image

Because I need to output in Json format later on, I need to transform it to an array of objects like this:

costGroup = [
                {"code": "GH"}, 
                {"code": "APT"}
            ]

I have been experiementing quite a bit with derived column transformation and with array functions but no success so far... any hint please?

Thanks in advance!

phemanth 15,855 Reputation points Microsoft External Staff Moderator

2024-07-01T13:57:34.5333333+00:00
@Jose Martinez

Thanks for the question and using MS Q&A platform.

In Azure Data Factory’s Mapping Data Flow, you can use the select transformation to achieve this. Here’s how you can do it:

Add a Select transformation after the source or transformation where you have the array column.

In the mapping tab of the Select transformation, add a new column.

Set the new column name as costGroup (or any name you prefer).

In the expression, use the following formula:

map(costGroup, (index, value) => { 'code': value })

This formula uses the map function to iterate over each element in the costGroup array (represented by value), and for each element, it creates a new object with a property code that holds the value of the element.

After this transformation, your costGroup array should be transformed into an array of objects as you desired.

Hope this helps. Do let us know if you any further queries.
Jose Martinez 0 Reputation points

2024-07-01T14:26:31.8+00:00
Hi thanks for taking the time and for the answer, however, in the Select transformation there is no Mapping tab:

There is a mapping section in the Select settings tab though. However, here only Fixed Mappings or Rule based mappings can be added, there is no way to add such an expresion like:

map(costGroup, (index, value) => { 'code': value })

I have tried to use this expression in a Derived Column transformation and it cannot be parsed...
Jose Martinez 0 Reputation points

2024-07-04T08:54:44.4633333+00:00

Hi @phemanth any additional hints here?
phemanth 15,855 Reputation points Microsoft External Staff Moderator

2024-07-04T11:08:55.42+00:00
@Jose Martinez

.Here's the revised approach:

Add a Select Transformation: As before, include a Select transformation after the source or the transformation that holds your array column (costGroup).

Create a New Column (Optional): Within the Select transformation, you can optionally create a new column to house the transformed array of objects. If you don't create a new column here, you can work with the original costGroup column in the subsequent Derived Column transformation.

Add a Derived Column Transformation: After the Select transformation, introduce a Derived Column transformation.

Define the Expression in Derived Column: In the Derived Column transformation, you'll create a new column to store the final array of objects. Here's the expression you can use:
derivedColumn = array(map(costGroup, (index, value) => ({'code': value}))) Explanation of the Expression:

array(...): This function wraps the results of the map function into a proper array of objects.

map(costGroup, (index, value) => ({'code': value})): This remains the same as before, iterating through the costGroup array and constructing objects with a code property for each element.

Here are some possible reasons why the expression might not be parsing correctly in your derived collum transformation

Missing closing parenthesis: Double-check the placement of parentheses in your expression. There should be a closing parenthesis at the end of the expression, after the value => { 'code': value } part.

Data type mismatch: The map function might be expecting a specific data type for the input array (costGroup). Verify that costGroup is indeed an array of strings. You can use the typeof function in the expression builder to check the data type: SQL
typeof(costGroup)
If it's not an array of strings, you might need to perform additional transformations before applying the map function.
Jose Martinez 0 Reputation points

2024-07-04T11:48:18.06+00:00

Hello @phemanth , thanks again, I appreciate the efforts but unfortunately that does not work either:

Actually, why do you use index and value? As per to the docu, the map function takes #item to map functions to the elements in array:

But even trying with #item, the syntax seems to not be the problem here, but I get an error that assignments are not supported:
phemanth 15,855 Reputation points Microsoft External Staff Moderator

2024-07-06T10:05:26.1966667+00:00
Hi @Jose Martinez

could you please try with this expression exactly mentioned below in derived Column

derivedColumn = array(map(costGroup, (item) => toString({ 'code': item })))

This expression uses the array function to wrap the results of the map function into a proper array of objects. The map function iterates through the costGroup array and constructs objects with a code property for each element. The toString function is used to convert the object into a string representation.

if the issue persists, please do let us know we will help you further on this.
phemanth 15,855 Reputation points Microsoft External Staff Moderator

2024-07-10T07:31:33.1466667+00:00

@Jose Martinez We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Jose Martinez 0 Reputation points

2024-07-10T08:08:37.1666667+00:00

Hi, the expression:

array(map(costGroup, (item) => toString({ 'code': item })))

you proposed did not work either unfortunately.
phemanth 15,855 Reputation points Microsoft External Staff Moderator

2024-07-11T11:29:30.6633333+00:00

@Jose Martinez

could you please share your data flow configuration and source settings, each activity screenshots, we will help you out.

Your answer

phemanth 15,855 Reputation points Microsoft External Staff Moderator

2024-07-01T13:57:34.5333333+00:00

@Jose Martinez

Thanks for the question and using MS Q&A platform.

In Azure Data Factory’s Mapping Data Flow, you can use the select transformation to achieve this. Here’s how you can do it:

Add a Select transformation after the source or transformation where you have the array column.

In the mapping tab of the Select transformation, add a new column.

Set the new column name as costGroup (or any name you prefer).

In the expression, use the following formula:

map(costGroup, (index, value) => { 'code': value })

This formula uses the map function to iterate over each element in the costGroup array (represented by value), and for each element, it creates a new object with a property code that holds the value of the element.

After this transformation, your costGroup array should be transformed into an array of objects as you desired.

Hope this helps. Do let us know if you any further queries.
Jose Martinez 0 Reputation points

2024-07-01T14:26:31.8+00:00

Hi thanks for taking the time and for the answer, however, in the Select transformation there is no Mapping tab:

There is a mapping section in the Select settings tab though. However, here only Fixed Mappings or Rule based mappings can be added, there is no way to add such an expresion like:

map(costGroup, (index, value) => { 'code': value })

I have tried to use this expression in a Derived Column transformation and it cannot be parsed...
Jose Martinez 0 Reputation points

2024-07-04T08:54:44.4633333+00:00

Hi @phemanth any additional hints here?
Jose Martinez 0 Reputation points

2024-07-04T11:48:18.06+00:00

Hello @phemanth , thanks again, I appreciate the efforts but unfortunately that does not work either:

Actually, why do you use index and value? As per to the docu, the map function takes #item to map functions to the elements in array:

But even trying with #item, the syntax seems to not be the problem here, but I get an error that assignments are not supported:
phemanth 15,855 Reputation points Microsoft External Staff Moderator

2024-07-06T10:05:26.1966667+00:00

Hi @Jose Martinez

could you please try with this expression exactly mentioned below in derived Column

derivedColumn = array(map(costGroup, (item) => toString({ 'code': item })))

This expression uses the array function to wrap the results of the map function into a proper array of objects. The map function iterates through the costGroup array and constructs objects with a code property for each element. The toString function is used to convert the object into a string representation.

if the issue persists, please do let us know we will help you further on this.
phemanth 15,855 Reputation points Microsoft External Staff Moderator

2024-07-10T07:31:33.1466667+00:00

@Jose Martinez We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Jose Martinez 0 Reputation points

2024-07-10T08:08:37.1666667+00:00

Hi, the expression:

array(map(costGroup, (item) => toString({ 'code': item })))

you proposed did not work either unfortunately.
phemanth 15,855 Reputation points Microsoft External Staff Moderator

2024-07-11T11:29:30.6633333+00:00

@Jose Martinez

could you please share your data flow configuration and source settings, each activity screenshots, we will help you out.

Share via

Transform a regular array into an array of objects inside a Mapping Data Flow in Azure Data Factory

Your answer