I think it relates to this issue [http://michaelcummings.net/mathoms/using-a-custom-jsonconverter-to-fix-bad-json-results/] Our procedure was like below: Create a Copy Activity in ADF pipeline: calling workday API, and sink response in Azure Data Lake Storage Gen2 as json Create a Data Flow in ADF to do some transformation, using the json files created in step1 Problem: When importing schema in Step 2, some of the fields expected to be array, but resulted in string. We found those fields which has bad schema might because that their types in json files are strange: when 2 elements in those fields, their types shows like array e.g. {Email_Address_Data: [{name:AAAA},{name:BBB}] }, but when there are less than 2 elements in those fields, their types shows as json. e.g. {Email_Address_Data: {name:AAAA} }, I asked question in https://learn.microsoft.com/en-us/answers/questions/1582355/azure-data-factory-dataflow-bad-schema-mapping but as I did not realize the root cause, so my expression in that question might look confusion, I raised a new question here.

Hi Zhenqi Liu I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that " The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer . Ask: I think it relates to this issue [http://michaelcummings.net/mathoms/using-a-custom-jsonconverter-to-fix-bad-json-results/] Our procedure was like below: Create a Copy Activity in ADF pipeline: calling workday API, and sink response in Azure Data Lake Storage Gen2 as json Create a Data Flow in ADF to do some transformation, using the json files created in step1 Problem : When importing schema in Step 2, some of the fields expected to be array, but resulted in string. We found those fields which has bad schema might because that their types in json files are strange: when 2 elements in those fields, their types shows like array e.g. {Email_Address_Data: [{name:AAAA},{name:BBB}] }, but when there are less than 2 elements in those fields, their types shows as json. e.g. {Email_Address_Data: {name:AAAA} }, Solution: We changed to use Python code instead of ADF, as the logic is really complex for workday API response. If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information. I hope this helps! If you have any other questions, please let me know. Thank you again for your time and patience throughout this issue. Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.

ADF data flow json sometimes array and sometimes object

Zhenqi Liu 30

I think it relates to this issue [http://michaelcummings.net/mathoms/using-a-custom-jsonconverter-to-fix-bad-json-results/]

Our procedure was like below:

Create a Copy Activity in ADF pipeline: calling workday API, and sink response in Azure Data Lake Storage Gen2 as json
Create a Data Flow in ADF to do some transformation, using the json files created in step1

Problem:

When importing schema in Step 2, some of the fields expected to be array, but resulted in string. We found those fields which has bad schema might because that their types in json files are strange: when 2 elements in those fields, their types shows like array e.g. {Email_Address_Data:[{name:AAAA},{name:BBB}]}, but when there are less than 2 elements in those fields, their types shows as json. e.g. {Email_Address_Data:{name:AAAA}},

I asked question in https://learn.microsoft.com/en-us/answers/questions/1582355/azure-data-factory-dataflow-bad-schema-mapping but as I did not realize the root cause, so my expression in that question might look confusion, I raised a new question here.

Harishga 5,990 Reputation points Microsoft Vendor

2024-03-14T04:30:53.84+00:00

@Zhenqi Liu
We are reaching out to the internal team to get more information related to your query and will get back to you as soon as we have an update.
Pramod o 155 Reputation points

2024-03-18T06:34:12.81+00:00
Hi @Zhenqi Liu

When importing a schema from Workday SOAP API to Azure Data Factory (ADF) using JSON format, encountering data type conversions from arrays to strings can be frustrating. Let’s explore the reasons behind this behavior and workaround solutions.

Workday SOAP API and Data Types:

The Workday SOAP API communicates with Workday services using the SOAP protocol, which primarily relies on XML-based messages.

When you retrieve data from Workday via the SOAP API, the response is structured as XML. The data types in the XML response are based on Workday’s internal representation.

JSON Conversion Challenges:

When converting XML to JSON, certain complexities arise due to differences in data modeling between the two formats.

Arrays in XML are often serialized as a sequence of elements with the same name. However, JSON treats arrays differently, expecting them to be enclosed in square brackets.

Why Arrays Become Strings:

The issue you’re facing—where data types from arrays are converted into strings—stems from how the SOAP API serializes complex data structures.

In XML, arrays are represented as a sequence of elements (e.g., <EmployeeID>) with the same name. When converting to JSON, these elements may be treated as individual string values.

Solutions to work around:

XML Parsing and Mapping: When you receive the XML response from the SOAP API, parse it correctly to extract relevant data. Map data types accurately during the conversion process. For example: If an array of integers is represented as a sequence of elements, convert it to a proper JSON array of integers. If an array of strings is expected, handle it accordingly.

Check Workday API Documentation: Refer to Workday’s official API documentation to understand the expected data structures and serialization rules. Look for specific guidance on handling arrays during conversion.

Validate and Test: Thoroughly test your integration to ensure that the converted JSON data aligns with your expectations. Inspect the intermediate steps to identify any issues related to data type conversions.

Custom Transformation: If necessary, write custom transformation logic to handle specific cases where arrays need special treatment. Consider using scripting or mapping functions within ADF to manipulate the data during the conversion process.

Debugging Tips:

Log intermediate steps during the conversion process to pinpoint where the data type conversions occur.

Compare the XML response with the expected JSON structure to identify discrepancies.****

Reference:
https://docs.workato.com/formulas/complex-data-types.html
https://stackoverflow.com/questions/7807957/generate-request-in-soapui-with-arrayofstring-type
https://stackoverflow.com/questions/13379418/how-to-send-string-array-in-soap-request-to-webservice

Remember that handling complex data structures during API integrations requires attention to detail. By understanding the serialization process and applying best practices, you can overcome the issue of array data types being converted into strings.

For more specific guidance related to your implementation, consult Workday’s documentation or seek assistance from experienced developers familiar with Workday integrations.
Zhenqi Liu 30 Reputation points

2024-03-19T01:29:34.24+00:00

Hi, @Pramod o

Thank you for your detailed explanation.

We understand the format difference between XML and Json, but thought setting json as sink in copy activity could handle the issue.

And thank you for your suggestion.

As the schema of Workday Soap API is very complex, we would write code instead of using ADF.

Best regards,

Carrie
Zhenqi Liu 30 Reputation points

2024-03-19T01:46:20.4566667+00:00

Hi @Pramod o

I'm not sure why my comment disappeared.

Thank you so much for your detailed explanation.

We understood the difference between XML and Json, but thought setting Json format as sink in Copy Activity of ADF would handle it.Thank you for your suggestion.

Considering the complex of Workday Soap API, we would write code to call the API and handle the transformation instead of using ADF.

Best regards,

Carrie
Harishga 5,990 Reputation points Microsoft Vendor

2024-03-19T08:30:07.4933333+00:00

Hi Zhenqi Liu
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.
Zhenqi Liu 30 Reputation points

2024-03-24T07:32:37.5333333+00:00

Hi @Harishga

Sorry for my late reply.

We changed to use Python code instead of ADF, as the logic is really complex for workday API response.

Should I close this ticket somewhere?
Harishga 5,990 Reputation points Microsoft Vendor

2024-03-26T05:15:25.5766667+00:00

Hi @Zhenqi Liu
We will handle the closure of this thread from our end.

Glad to know your issue has been resolved. Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others "I'll repost your solution in case you'd like to accept the answer.

Accepted answer

Harishga 5,990 Reputation points Microsoft Vendor

2024-04-02T05:11:00.05+00:00
Hi Zhenqi Liu
I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer .

Ask:
I think it relates to this issue [http://michaelcummings.net/mathoms/using-a-custom-jsonconverter-to-fix-bad-json-results/]

Our procedure was like below:

Create a Copy Activity in ADF pipeline: calling workday API, and sink response in Azure Data Lake Storage Gen2 as json

Create a Data Flow in ADF to do some transformation, using the json files created in step1

Problem :

When importing schema in Step 2, some of the fields expected to be array, but resulted in string. We found those fields which has bad schema might because that their types in json files are strange: when 2 elements in those fields, their types shows like array e.g. {Email_Address_Data:[{name:AAAA},{name:BBB}]}, but when there are less than 2 elements in those fields, their types shows as json. e.g. {Email_Address_Data:{name:AAAA}},

Solution:
We changed to use Python code instead of ADF, as the logic is really complex for workday API response.

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

I hope this helps!

If you have any other questions, please let me know. Thank you again for your time and patience throughout this issue.

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

ADF data flow json sometimes array and sometimes object

0 additional answers

Your answer