Azure Data Factory copy nested JSON to SQL table

Question

Does anyone have an easy way to convert nested JSON to a flat SQL table? Just want to repeat the higher level data on each of the lower level detail. It looks like it can be done in mapping, I have tried as per the MS documentation but got a table full of NULL. Here is what I have tried and the result. json

Option 1 Result: Only returns the first record of the ‘assignedLicences’ Option1

Option 2: Returns multiple ‘assignedLicenses’ for each user, but only returns the first user id in each page. Option2

Option3: as per the MS documentation Result: returns all NULL values Option3

Tried using the advanced mapping to assign different levels, I think there is a solution in there somewhere but cannot get it. Any suggestions you have I am happy to try, I'm getting nowhere. — Corey Edwards
– Corey Edwards, Commented Jul 1, 2020 at 5:32

Steve Johnson · Accepted Answer · 2020-07-01 08:46:22Z

1

You can have a try:

1.click import schemas button

2.if you have a JsonArray,select it.

3.you can directly see and edit the fields' JSON paths by opening Advanced editor .

Here is a Microsoft documentation about it.Please refer to this.

Hope this can help you.

answered Jul 1, 2020 at 8:46

Steve Johnson

8,8081 gold badge11 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Corey Edwards Over a year ago

Thanks Steve, gave it a go exactly as per the MS documentation but end up with a table full of NULL. My closest example is using a similar collection reference, but I only get the first record for each page of data. Wondering if I need to look at my paging?

Steve Johnson Over a year ago

Can you provide a sample like your json file?

Corey Edwards Over a year ago

Hi Steve, I added an image to the original post. Will that do? I cant post the file as it contains confidential information.

wBob Over a year ago

Where is the JSON stored? If it's already in Azure Blob Storage then Azure SQL Database can import it natively using OPENROWSET.

Collectives™ on Stack Overflow

Azure Data Factory copy nested JSON to SQL table

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related