ADF Data flow fetch data from JSON to SQL

Question

ADF Data flow fetch data from JSON to SQL

Mav 81

Hi
Could someone advise how to use DATAFLOW in ADF to get data from a JSON with hierarchy
My JSON is of following structure

{
"totalRowCount": 2,
"data": [
{
"ProductCode": "P - 1",
"ProductType": "CARS",
"fields": [
{
"fieldName": "CarType",
"label": "Is this an electric car?",
"dataTypeName": "RADIOBUTTON",
"fields": null,
"values": [
{
"value": "No"
}
]
},
{
"fieldName": "CarLocation",
"label": "Where is the Vendor located",
"dataTypeName": "SINGLESELECT",
"fields": null,
"values": [
{
"value": "123 NY Park NY City USA"
}
]
}
]
},
{
"ProductCode": "P - 2",
"ProductType": "BUS",
"fields": [
{
"fieldName": "BusType",
"label": "Is this an electric Bus?",
"dataTypeName": "RADIOBUTTON",
"fields": null,
"values": [
{
"value": "No"
}
]
},
{
"fieldName": "BusLocation",
"label": "Where is the Vendor located",
"dataTypeName": "SINGLESELECT",
"fields": null,
"values": [
{
"value": "4444 NY Park NY City USA"
}
]
}
]
}
]
}

Accepted answer

1 additional answer

Your answer

Answer 1

AnnuKumari-MSFT 34,556 Microsoft Employee Moderator

Hi @devhere ,
Thanks for using Microsoft Q&A platform and posting your question.
The Json provided by you consists of Array of multiple arrays which means you need to use multiple Flatten transformation to get the job done. Please refer to the below attached gif for your reference .

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

Mav 81 Reputation points

2022-02-01T01:35:33.04+00:00

Thanks Annukumari,

I tried doing this and it sort of worked
However, I need to use my REST API connector as Source in dataflow.
If I use a JSON file as source Source identified schema correctly and flattening works however If I use the REST API the source is not able to detect the schema and hence cant use flattening

Screenshots API call. I chose source as JSON array in source options

In this data preview we can't see hierarchy

These below calls are to JSON

Here the data preview shows DATA hierarchy
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2022-02-01T11:27:30.207+00:00

@devhere ,
What do you get to see when you hover over the data array in preview for REST API source?
Could you please try to use copy data activity in your pipeline where source is the REST API and sink is JSON file which will then hold the data in .json file.
Then call the data flow in the pipeline . In that way, you don't have to struggle to use flatten transformation on REST API source. If you have any further query do let us know. Kindly do click Accept Answer and Up-Vote if the above suggestion was helpful.
Mav 81 Reputation points

2022-02-01T12:57:10.21+00:00

Thanks Annu for quick response. For copy data do I need to provide a dynamic mapping . Actually this JSON is having multiple levels of hierarchy so when I try import schema it doesn’t detect it correctly

I tried using a web task that returns JSON which I then pass to a stored Proc that flattens it however that webtask fails when payload is bigger so I shifted to data flow . Any way we can fix “big payload” error
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2022-02-01T13:18:55.267+00:00

Hi @devhere ,
In that case how about use dataflow1 to copy data from source(REST API) to sink(JSON) then use dataflow2 as suggested above for JSON file as source. And call both dataflow1,dataflow2 one after another in the pipeline. Basically, my point is to ensure your data has landed in Json file before transformation. If this answers your query, kindly do click Accept Answer and Up-Vote for the same. If you have any further query do let us know.
Mav 81 Reputation points

2022-02-02T08:16:26.453+00:00

Hi Annu
I have successfully used COPY DATA to write a JSON file and then read that file via data flow task and create a flattened file,
However, I am facing small issue as some values in JSON have comma (,) within the text and records are splitting if I try to use copydata to read the flattened file. In SSIS we use Text identifier field but not sure what to use in ADF. Here's the link https://learn.microsoft.com/en-us/answers/questions/719218/text-qualifier-in-adf-blob-source.html I think thats the last thing
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2022-02-02T13:53:35.14+00:00

Hi @devhere ,
Just checking in to see if the above answer helped. If this answers your query, do click Accept Answer and Up-Vote for the same as accepted answer helps community as well. And, if you have any further query do let us know.
Mav 81 Reputation points

2022-02-03T02:07:31.503+00:00

Thanks It helped. I am marking it as an answer. could you help in other adf issue
https://learn.microsoft.com/en-us/answers/questions/719218/text-qualifier-in-adf-blob-source.html
arkiboys 9,706 Reputation points

2022-03-13T08:00:23.217+00:00

Hello,
How is it possible to slow down this video example.
I can not follow it as it goes too fast for me
Thanks

Answer 2

Y Varsa Anand 0

Hi Annu,

i have multiple json file in sink, with the one json file i'm able to do flatten and its successfully copying. can u please help for multiple json file i'm not able to do it.

can u provide any suggestion.

Thanks

AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2023-02-14T14:54:56.04+00:00

Hi Y Varsa Anand ,

Since it's an old thread, requesting you to create a new thread with details of your source json data. Thanks.

Share via

ADF Data flow fetch data from JSON to SQL

1 additional answer

Your answer