How to parse nested json array of document in ADF data flow

Question

How to parse nested json array of document in ADF data flow

venkat rao 65

Hi all
I am trying to fitch the values from a nested josh array of document , I have used aggregate to convert into objects but not able to fitch the values of all child nodes like as below
itOffer.item
itOffer.item.SplOfr
itOffer.item.buy
itOffer.item.lGroup.location.item[]
itOffer.item.zdeal.item[]
itOffer.item.incentive.item[]

required your assistance , please find below

Source :- Inline data set, array of document User's image

User's image

Json Array of document
[

{"itOffer":{"item":{"SplOfr":{"TypeCode":null,"From":20210510000000,"buy":{"item":{"avaab":0,"annn":null,"babab":2,"SequenceNr":1,"Amount":".01000","diType":"04"}},"parentOffer":"ag==","To":20210520235959,"offerDesc":"Created for ST 13","offerSetId":"ag==","refEventName":null,"refOfferId":"abcd==","get":{"item":{"gsgs":0,"variantSprof":null,"sghshs":2,"agsg":1,"minAmount":".01000","gshxh":"EA","unitForecast":0}},"limit":0,"refId":"0000000000","lGroup":{"NodeId":"AAAAAAAAAAAAAAAAAAAAAA==","eNodeId":null,"oGroup":null,"exterId":null,"parentId":"AAAAAAAAAAAAAAAAAAAAAA==","parentGroupId":"AAAAAAAAAAAAAAAAAAAAAA==","lHId":"0000000000","location":{"item":[{"offerId":"AAAAAAAAAAAAAAAAAAAAAA==","exterId":"ZST2","loId":"AA062mkBHtujxXcjAoc1fA==","lot":1040},{"offerId":"AAAAAAAAAAAAAAAAAAAAAA==","exterId":1700,"loId":"AA062mkBHtujxXcbt1TVfA==","lot":1040}]},"externalLocationHierarchyDesc":null,"externalLocationHierarchyNodeId":null,"locationHierarchyTypeCode":null,"locationGroupId":"AAAAAAAAAAAAAAAAAAAAAA=="},"promotionType":"DOF","externalLeadingCategoryId":null,"unitOfMeasure":null,"offerSetName":null,"leadingOffer":"AA062mkBHtutql1wc9l2tw==","intExternalOfferId":"000000000000000000000000000000000101","offerTypeCode":1,"retailEventId":null,"forecastTstmp":0,"liftTypeCode":"00","financialCalcRequired":null,"mdSystem":10001,"enforceMultiple":"X","name":"Sample_5","zdeal":{"item":[{"dNumber":"0000006966","term":"21FB2021","vFrom":"20240410000000","vTo":"20240507235959"}]},"incentive":{"item":[{"externalId":"2063828089181","iId":"21FB20210B791EEEB9BE77FC3411ED9E","iPtId":"00000000000000000000000000000000","inCls":"Z3","inTypeCode":"Z31"}]}}}}},

{"itOffer":{"item":{"SplOfr":{"TypeCode":null,"From":20210510000000,"buy":{"item":{"avaab":0,"annn":null,"babab":2,"SequenceNr":1,"Amount":".01000","diType":"04"}},"parentOffer":"ag==","To":20210520235959,"offerDesc":"Created for ST 13","offerSetId":"ag==","refEventName":null,"refOfferId":"abcd==","get":{"item":{"gsgs":0,"variantSprof":null,"sghshs":2,"agsg":1,"minAmount":".01000","gshxh":"EA","unitForecast":0}},"limit":0,"refId":"0000000000","lGroup":{"NodeId":"AAAAAAAAAAAAAAAAAAAAAA==","eNodeId":null,"oGroup":null,"exterId":null,"parentId":"AAAAAAAAAAAAAAAAAAAAAA==","parentGroupId":"AAAAAAAAAAAAAAAAAAAAAA==","lHId":"0000000000","location":{"item":[{"offerId":"AAAAAAAAAAAAAAAAAAAAAA==","exterId":"ZST2","loId":"AA062mkBHtujxXcjAoc1fA==","lot":1040},{"offerId":"AAAAAAAAAAAAAAAAAAAAAA==","exterId":1700,"loId":"AA062mkBHtujxXcbt1TVfA==","lot":1040}]},"externalLocationHierarchyDesc":null,"externalLocationHierarchyNodeId":null,"locationHierarchyTypeCode":null,"locationGroupId":"AAAAAAAAAAAAAAAAAAAAAA=="},"promotionType":"DOF","externalLeadingCategoryId":null,"unitOfMeasure":null,"offerSetName":null,"leadingOffer":"AA062mkBHtutql1wc9l2tw==","intExternalOfferId":"000000000000000000000000000000000101","offerTypeCode":1,"retailEventId":null,"forecastTstmp":0,"liftTypeCode":"00","financialCalcRequired":null,"mdSystem":10001,"enforceMultiple":"X","name":"Sample_5","zdeal":{"item":[{"dNumber":"0000006966","term":"21FB2021","vFrom":"20240410000000","vTo":"20240507235959"}]},"incentive":{"item":[{"externalId":"2063828089181","iId":"21FB20210B791EEEB9BE77FC3411ED9E","iPtId":"00000000000000000000000000000000","inCls":"Z3","inTypeCode":"Z31"}]}}}}}

]

Anonymous

2024-05-03T08:29:42.8766667+00:00
@venkat rao

Thanks for using MS Q&A platform and posting your query.

The error message in the image indicates that the ADF data flow encountered an issue while parsing the JSON document. Specifically, the error states that the flattenitOffer@ItOfferflatten node does not return a string type, which is causing the failure.

Here's how to parse a nested JSON array of documents in ADF data flow using the Flatten transformation:

Add the source data:

In your ADF data flow, start by adding your source data. This could be an inline dataset containing the JSON array you provided in the image or a reference to an external data source like Azure Blob Storage.

Flatten the nested arrays:

Use the Flatten transformation to unnest the nested arrays in your JSON document. You can configure the Flatten transformation by specifying the name of the array you want to unnest in the "Unroll by" property.

Here's a breakdown of how to achieve this using the information in the image:

In the image, the nested arrays you want to unnest are itOffer.item.lGroup.location.item, itOffer.item.zdeal.item, and itOffer.item.incentive.item.

You can add three Flatten transformations to your data flow. Configure each Flatten transformation with the following settings:

Unroll by: Set this property to the path of the nested array you want to unnest. For example, for the first Flatten transformation, set it to itOffer.item.lGroup.location.item.

Handle data types:

It's important to ensure that the data types of the columns produced by the Flatten transformation match the data types you expect in your output. You can use the Data Conversion transformation to convert data types if necessary.

Sink the data:

After flattening the nested arrays and handling data types, you can sink the data to your destination. This could be a data lake in Azure Data Storage (ADS), a database table, or another supported data sink.

Hope this helps. Do let us know if you any further queries.
Anonymous

2024-05-07T08:14:09.31+00:00

@venkat rao 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.
venkat rao 65 Reputation points

2024-05-16T13:43:20.7033333+00:00

Hi phemanth , Sorry for delay in my response as I was not feeling well from past few days
Could you please elaborate more to build the data transformation , Required your assistance
Anonymous

2024-05-17T11:34:38.7933333+00:00
Hi @venkat rao

I see in image you sent shows an error message in Azure Data Factory (ADF). The error message is:

DF-PARS-004 at Parse 'parse1'(Line 24/Col 15): Resulting node flattenitOffer@ItOfferflatten does not return a string type

This error means that the ADF pipeline is encountering a problem when trying to parse JSON data. Specifically, the node named flattenitOffer@ItOfferflatten is not returning a string value, as expected by the pipeline.

Here are some possible causes of this error:

The JSON data that you are trying to parse is not formatted correctly. There may be a missing quotation mark or a misplaced comma.

The flattenitOffer@ItOfferflatten node is not configured correctly. This node is likely responsible for transforming the JSON data in some way. It may be trying to access a field that does not exist in the JSON data, or it may be trying to perform an operation that is not supported on the data type.

Here are some steps you can take to troubleshoot the error:

Check the format of the JSON data that you are trying to parse. You can use a JSON validator tool to help you identify any errors in the formatting.

Review the configuration of the flattenitOffer@ItOfferflatten node. You can find documentation on ADF data flow nodes in the Microsoft documentation https://learn.microsoft.com/en-us/azure/data-factory/concepts-data-flow-overview.

Try using the ADF data preview feature to inspect the data at different stages of the pipeline. This can help you to identify where the error is occurring.

If you are still having trouble resolving the error, please do let us know we will help you out.

after resolving the error Here's a detailed guide:

1. Create Data Flow:

In Azure Data Factory, navigate to the "Data Flows" section and create a new data flow.

2. Add Source Data:

In the data flow editor, choose the source for your JSON data. You have two options:

Inline Dataset: - If your JSON data is readily available, you can create an inline dataset. Click on "Source" and select "JSON". Paste your JSON code within the inline dataset editor.

External Source: - If your JSON data resides in an external location like Azure Blob Storage, select the appropriate connector from "Source" (e.g., Azure Blob Storage). Configure the connection details and specify the path to your JSON file.

3. Add Flatten Transformations:

We need to unnest three nested arrays: itOffer.item.lGroup.location.item, itOffer.item.zdeal.item, and itOffer.item.incentive.item. Add three separate Flatten transformations to your data flow.

4. Configure Flatten Transformations:

Double-click on the first Flatten transformation to open its settings.

Name: Assign a descriptive name (e.g., "FlattenLocations"). Input: Select the source dataset containing your JSON data. Unroll by: Enter the path to the first nested array you want to unnest, which is itOffer.item.lGroup.location.item.

Repeat for Remaining Flatten Transformations:

Repeat steps 4a-4c for the remaining two Flatten transformations. Configure them with the following settings:

Second Flatten:

Name: FlattenZdeals (or similar)

Unroll by: itOffer.item.zdeal.item

Third Flatten: Name: FlattenIncentives (or similar) Unroll by: itOffer.item.incentive.item

5. Preview Data (Optional):

You can preview the output of each transformation by right-clicking on it and selecting "Preview Data". This helps verify if the flattening is happening as expected.

6. Additional Transformations (Optional):

Depending on your needs, you might want to perform further transformations on the data. Here are some possibilities:

Data Conversion: Use the Data Conversion transformation to convert data types if necessary (e.g., converting strings to numbers). Derive Columns: Create additional columns based on existing data using the Derive Column transformation.

7. Sink the Data:

Finally, define the destination for your transformed data. Click on "Sink" and choose the desired connector (e.g., Azure Data Lake Storage, Database). Configure the connection details and specify the output schema.

8. Run the Data Flow:

Once everything is configured, click on "Run" to execute the data flow. This will process your JSON data, flatten the nested arrays, and store the transformed data in your chosen destination.

Hope this helps. Do let us know if you any further queries.
venkat rao 65 Reputation points

2024-05-22T08:57:45.5533333+00:00

Hi phemanth , Data Transformation working as excepted have used aggregate too collect all the array objects to parse and used multiple flattens for all the arrays I had It's working as you suggested
I am trying another way around , I have used stringify for converting and added a parse activity to next to parse parent Json nodes and then added flatten to populate the Json nodes
But when I check data previews the values are populating as null,
Require your assistance
venkat rao 65 Reputation points

2024-05-22T12:17:40.45+00:00

And more thing with your approach if I build the transformation using only flatten by default we have acctual column data types , But I want all the flatten nodes in the string format
Anonymous

2024-05-23T08:27:51.8833333+00:00
@venkat rao the null values issue you're encountering in your Azure Data Factory (ADF) pipeline when using stringify and parse activities. Here are some consolidated troubleshooting steps combining the best aspects of previous suggestions and addressing potential causes:

1. Verify JSON Structure and Stringify Expression:

Meticulously examine your JSON data: Ensure there are no unexpected null values within the parent JSON nodes you intend to parse. Double-check for missing keys or incorrect data types.

Scrutinize the stringify expression: Make sure it correctly captures the parent JSON nodes you want to parse later. Utilize the data preview feature to inspect the output of the stringify activity and confirm it's generating the expected string representation.

2. Scrutinize Parse Activity Configuration:

Input: Double-check that the input to the parse activity is the output column generated by the stringify activity.

Settings:

Format: Verify that the "Format" setting in the parse activity matches the actual format of your JSON data (likely JSON).

Root element: If your JSON has a single root element, specify its name in the "Root element" setting. Leave it blank for complex JSON without a single root.

Encoding: Ensure the "Encoding" setting aligns with the encoding of your JSON data (typically UTF-8).

3. Inspect Data Previews:

Stringify output: Use the data preview after the stringify activity to confirm it's generating valid JSON strings.

Parse output: Check the data preview after the parse activity to see if the parsing is successful and the parsed data populates into separate columns as expected.

4. Consider Data Type Mismatches:

If the stringify activity produces unexpected data types like arrays or objects, use a data conversion activity before the parse activity to convert it to a string.

Ensure the parse activity's output column data types match the expected types of the parsed data. Use data conversion activities after the parse activity if necessary.

using only flatten transformations in ADF will typically preserve the original data types of the nested elements. Here are two approaches to achieve all flattened nodes in string format:

Approach 1: Data Conversion After Flatten

Flatten as usual: Use flatten transformations to unnest the desired nested arrays in your JSON data.

Data Conversion: Add a Data Conversion transformation after each flatten step.

Configure Conversion: In the Data Conversion transformation, set the "Source" column to the output column from the corresponding flatten activity.

Target Type: Select "String" as the target data type for each conversion activity.

This approach ensures that the flattened data is converted to strings after the flattening process is complete.

Approach 2: Custom Function with Flatten

Create a Custom Function: Define a custom function in ADF using Data Flow Script activity. This function will take a value and return its string representation. Here's an example using JavaScript: JavaScript
function function toString(value) { return JSON.stringify(value); }
Use Flatten with Custom Function: In the flatten transformation, instead of directly selecting the nested column to unroll by, use an expression that calls your custom function with the nested column. This expression will convert the nested element to a string before flattening. For example, if the nested column to unroll by is called "nestedData", the expression would be toString(nestedData).

This approach directly converts the nested elements to strings during the flattening process itself.

Choosing the Right Approach:

Approach 1 is simpler to implement but might require more data conversion activities depending on the number of flatten steps.

Approach 2 involves creating a custom function but can be more concise if you have many flatten operations
venkat rao 65 Reputation points

2024-05-23T17:21:28.6166667+00:00

Hi All , Please guide me require your assistance
Anonymous

2024-05-24T09:38:47.0233333+00:00

@venkat rao please check my response above. Do let us know if you any further queries on my response.