Hi, My source is a JSON which is not formatted properly I want to assign columns[0][name] as a column name to rows[0][0], I'm trying do it through column mapping in copy data but it is throwing below error Does anyone have idea on how to map them? Thank you!

Hi @Pujitha Chennupati , As I understand the ask , it seems you are trying to map JSON properties to SQL columns but its throwing error. Please correct me if I am wrong. In copy activity only one array can be flattened using collection reference . You can consider using DataFlow for flattening multiple arrays by using consecutive Flatten transformations. Click here to know more about Flatten transformations in dataflow. Hope this will help. Please let us know if any further queries. ------------------------------ Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how Want a reminder to come back and check responses? Here is how to subscribe to a notification If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

Trying to Map columns dynamically withing JSON file

Accepted answer

AnnuKumari-MSFT 32,736 Reputation points Microsoft Employee

2022-07-12T16:33:10.56+00:00
Hi @Pujitha Chennupati ,

As I understand the ask , it seems you are trying to map JSON properties to SQL columns but its throwing error. Please correct me if I am wrong.

In copy activity only one array can be flattened using collection reference . You can consider using DataFlow for flattening multiple arrays by using consecutive Flatten transformations.

Click here to know more about Flatten transformations in dataflow.

Hope this will help. Please let us know if any further queries.

------------------------------

Please don't forget to click on or upvote button whenever the information provided helps you.
Original posters help the community find answers faster by identifying the correct answer. Here is how

Want a reminder to come back and check responses? Here is how to subscribe to a notification

If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators
Please sign in to rate this answer.

1 person found this answer helpful.
AnnuKumari-MSFT 32,736 Reputation points Microsoft Employee

2022-07-18T06:39:08.347+00:00

Hi @Pujitha Chennupati ,
Just checking in to see if the above answer helped. Please do consider clicking Accept Answer and Up-Vote for the same as accepted answers help community as well. If you have any further query do let us know.

Pujitha Chennupati 41 Reputation points

2022-07-18T09:20:14.357+00:00

Hi @AnnuKumari-MSFT ,

Thank you for your response.

I tried flattening the JSON file but when I'm connecting the JSON file that is stored in the blob storage it is taking the JSON file as 'Document per line' format
and in the output I can only see first column data which is in integer format

AnnuKumari-MSFT 32,736 Reputation points Microsoft Employee

2022-07-26T07:02:41.84+00:00

Hi @Pujitha Chennupati ,

By default, 'Document per line' would be coming as document format in JSON settings. You need to change it to 'Array of documents' for your scenario.

Hope it helps!

------------------------

Please do consider clicking Accept Answer and Up-Vote for the same as accepted answers help community as well. If you have any further query do let us know.

Pujitha Chennupati 41 Reputation points

2022-07-26T18:01:11.02+00:00

Above picture is the source format where I'm getting rows and columns separately. For Incident ID column the value would be 13084 for created date time the value would be 8/7/2021

It is throwing me error : 'Malformed records are detected in schema inference. Parse Mode: FAILFAST' when I change it to 'Array of documents'

AnnuKumari-MSFT 32,736 Reputation points Microsoft Employee

2022-08-01T05:38:30.093+00:00

Hi @Pujitha Chennupati ,
The source data screenshot that you shared doesn't look like a valid JSON . If you see the data inside Row[] array , the data is not in key value pair. You can validate the same using online JSON validator tools, for example: https://jsonformatter.curiousconcept.com/#

In ADF , data transformation works based on the source schema. Could you please consider asking the source team to provide the data in proper JSON format, so that flattening of data is achievable.

------------------

Please do consider clicking Accept Answer as accepted answers help community as well. If you have any further query do let us know.
Sign in to comment

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

Share via

Trying to Map columns dynamically withing JSON file

0 additional answers

Your answer