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

Question

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

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

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

Answer 1

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.

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.

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

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.