ADF Data Flows Flatten nested json array values are being populated as null

venkat rao 65 Reputation points
2024-05-30T11:06:56.13+00:00

Hi All

I am building a data transamination with ADF data flow using a nested json array of objects , but after parse and flatten the json node itOffer.item.LeadOfer.zdeal.item[].dealNumber I am seeing that the column values are populated as null .
I have build my transamination as follows
step 1:- itOffer.item.
At starting I have used stringify to convert and then parse
( item as string)[] followed by flatten to get itOffer.item.
step 2:- itOffer.item.LeadOfer.
After that i have parsed gain itOffer.item

(
 currency as string, 
internalSalesOrg as string,
leadOfr as string
)[]

followed by flatten to get itOffer.item.LeadOfer.
Step 3 :- itOffer.item.LeadOfer.zdeal
again parsed itOffer.item.LeadOfer

(
 extExternalOfferId as string,
 zdeal as string,
 offerId as string,
 timestampFrom as string,
 timestampTo as string,
 unitOfMeasure as string,
 name as string,
 offerDesc as string,
 promotionType as string,
 stateCode as string,
 zzRereleaseFlag as string,
 zzOfferDayList as string,
 limit as string,
 changedBy as string,
 changedOn as string,
 createdOn as string,
)[]

flatten to get itOffer.item.LeadOfer.zdeal ,until here I am good
Step 4:- itOffer.item.LeadOfer.zdeal.item[]
I am again used parsed itOffer.item.LeadOfer.zdeal as ( item as string)[] and fatten to get itOffer.item.LeadOfer.zdeal.item[] , we can see the values in data preview also
User's image

User's image

Step 5 :- itOffer.item.LeadOfer.zdeal.item[].dealNumber
I have parsed again ( dealNumber as string[])[] and flatten to the the value of itOffer.item.LeadOfer.zdeal.item[].dealNumber
Here deal number values are showing as null
required your assistance
User's image

User's image

The sample json I have used

{

"importParameterTimestamp": "20240325084320",

"itOffer": {

  "item": {

    "leadOfr": {

      "extExternalOfferId": "000000000000000000000000000000438304",

      "offerId": "790C8D82EB0F",

      "leadingOffer": "A8C790C8D82EB0F",

      "parentOffer": "00000000000000000000000000000000",

      "timestampFrom": "20240327000000",

      "timestampTo": "20240402235959",

      "stateCode": "10",

      "limit": "255",

      "offerDesc": "Save up to 20 per cent",

      "offerSetId": "00000000000000000000000000000000",

      "zzCollisionsReviewedOn": "20240322155944",

      "zzOrgValfr": "20240327000000",

      "zzOrgValto": "20240402235959",

      "createdBy": "40043512",

      "createdOn": "20240322155018",

      "changedBy": "40208413",

      "changedOn": "20240325084316",

      "unitOfMeasure": "B11",     

      "zdeal": {

        "item": [

          {

            "dealNumber": "0000009703",

            "term": "EBA8C77DEBB678B0F",

            "validFrom": "20240327000000",

            "validTo": "20240402235959"

          },

          {

            "dealNumber": "0000009704",

            "term": "C77F56949AB0F",

            "validFrom": "20240327000000",

            "validTo": "20240402235959"

          },

          {

            "dealNumber": "0000009705",

            "term": "77F5BEB8CB0F",

            "validFrom": "20240327000000",

            "validTo": "20240402235959"

          }

        ]

      }

  }

}

}

Note :- As a workaround we can use Aggregate to collect and add flatten , but by default flatten considering the data types of values and we can't over write the data types
so I am not interested in that approach I want all values need to be in string format and I will use derived column to convent strings into acctual data types before loading into sink

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,553 questions
Azure Databricks
Azure Databricks
An Apache Spark-based analytics platform optimized for Azure.
2,015 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,893 questions
{count} votes

Accepted answer
  1. Harishga 4,880 Reputation points Microsoft Vendor
    2024-06-04T07:47:15.89+00:00

    Hi @venkat rao
    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 .

    I am building a data transamination with ADF data flow using a nested json array of objects , but after parse and flatten the json node itOffer.item.LeadOfer.zdeal.item[].dealNumber I am seeing that the column values are populated as null . I have build my transamination as follows

    step 1:- itOffer.item. At starting I have used stringify to convert and then parse ( item as string)[] followed by flatten to get itOffer.item.
    step 2:- itOffer.item.LeadOfer. After that i have parsed gain itOffer.item

    (
     currency as string, 
    internalSalesOrg as string,
    leadOfr as string
    )[]
    

    followed by flatten to get itOffer.item.LeadOfer.
    Step 3 :- itOffer.item.LeadOfer.zdeal again parsed itOffer.item.LeadOfer

    (
     extExternalOfferId as string,
     zdeal as string,
     offerId as string,
     timestampFrom as string,
     timestampTo as string,
     unitOfMeasure as string,
     name as string,
     offerDesc as string,
     promotionType as string,
     stateCode as string,
     zzRereleaseFlag as string,
     zzOfferDayList as string,
     limit as string,
     changedBy as string,
     changedOn as string,
     createdOn as string,
    )[]
    

    flatten to get itOffer.item.LeadOfer.zdeal ,until here I am good
    Step 4:- itOffer.item.LeadOfer.zdeal.item[] I am again used parsed itOffer.item.LeadOfer.zdeal as ( item as string)[] and fatten to get itOffer.item.LeadOfer.zdeal.item[] , we can see the values in data preview also.
    User's image

    User's image

    Step 5 :- itOffer.item.LeadOfer.zdeal.item[].dealNumber I have parsed again ( dealNumber as string[])[] and flatten to the the value of itOffer.item.LeadOfer.zdeal.item[].dealNumber Here deal number values are showing as null required your assistance.
    User's image

    User's image

    The sample json I have used{

    "importParameterTimestamp": "20240325084320",
    
    "itOffer": {
    
      "item": {
    
        "leadOfr": {
    
          "extExternalOfferId": "000000000000000000000000000000438304",
    
          "offerId": "790C8D82EB0F",
    
          "leadingOffer": "A8C790C8D82EB0F",
    
          "parentOffer": "00000000000000000000000000000000",
    
          "timestampFrom": "20240327000000",
    
          "timestampTo": "20240402235959",
    
          "stateCode": "10",
    
          "limit": "255",
    
          "offerDesc": "Save up to 20 per cent",
    
          "offerSetId": "00000000000000000000000000000000",
    
          "zzCollisionsReviewedOn": "20240322155944",
    
          "zzOrgValfr": "20240327000000",
    
          "zzOrgValto": "20240402235959",
    
          "createdBy": "40043512",
    
          "createdOn": "20240322155018",
    
          "changedBy": "40208413",
    
          "changedOn": "20240325084316",
    
          "unitOfMeasure": "B11",     
    
          "zdeal": {
    
            "item": [
    
              {
    
                "dealNumber": "0000009703",
    
                "term": "EBA8C77DEBB678B0F",
    
                "validFrom": "20240327000000",
    
                "validTo": "20240402235959"
    
              },
    
              {
    
                "dealNumber": "0000009704",
    
                "term": "C77F56949AB0F",
    
                "validFrom": "20240327000000",
    
                "validTo": "20240402235959"
    
              },
    
              {
    
                "dealNumber": "0000009705",
    
                "term": "77F5BEB8CB0F",
    
                "validFrom": "20240327000000",
    
                "validTo": "20240402235959"
    
              }
    
            ]
    
          }
    
      }
    
    }
    

    }

    Note :- As a workaround we can use Aggregate to collect and add flatten , but by default flatten considering the data types of values and we can't over write the data types

    so I am not interested in that approach I want all values need to be in string format and I will use derived column to convent strings into actual data types before loading into sink.

    Solution :
    Since the file was array of document, I have chanced the Parse Json setting to array of document and able to the values present
    User's image

    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.

    0 comments No comments

0 additional answers

Sort by: Most helpful