RyanAbbey-0701 avatar image
1 Vote"
RyanAbbey-0701 asked ShaikMaheer-MSFT commented

API (JSON) to Parquet via DataFactory

I'm trying to investigate options that will allow us to take the response from an API call (ideally in JSON but possibly XML) through the Copy Activity in to a parquet output.. the biggest issue I have is that the JSON is hierarchical so I need it to be able to flatten the JSON

Initially, I've been playing with the JSON directly to see if I can get what I want out of the Copy Activity with intent to pass in a Mapping configuration to meet the file expectations (I've uploaded the Copy activity pipe and sample json, not sure if anything else is required for play)

On initial configuration, the below is the mapping that it gives me... of particular note is the hierarchy for "vehicles" (level 1) and (although not displayed because I can't make the screen small enough) "fleets" (level 2 - i.e. attribute of vehicle)

The output when run is giving me a single row but my data has 2 vehicles with 1 of those vehicles having 2 fleets.. I have set the Collection Reference to "Fleets" as I want this lower layer (and have tried "[0]", "[*]", "") without it making a difference to output (only ever first row), what should I be setting here to say "all rows"? (If I do the collection reference to "Vehicles" I get two rows (with first Fleet object selected in each) but it must be possible to delve to lower hierarchies if its giving the selection option?? Or is this for multiple level 1 hierarchies only? Is it possible to get to level 2?


image.png (32.7 KiB)
inputjson.txt (1.3 KiB)
pipe.txt (9.8 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @RyanAbbey-0701 ,

Just checking in to see if the below answer helped. If this answers your query, do click 130616-image.png and upvote 130671-image.png for the same. And, if you have any further query do let us know.

0 Votes 0 ·

1 Answer

ShaikMaheer-MSFT avatar image
1 Vote"
ShaikMaheer-MSFT answered ShaikMaheer-MSFT commented

Hi @RyanAbbey-0701 ,

Thank you for posting query on Microsoft Q&A Platform.

Yes, Its limitation in Copy activity. Copy activity will not able to flatten if you have nested arrays. A workaround for this will be using Flatten transformation in data flows.

Similar example with nested arrays discussed here. Please check it. I tried flatten transformation on your sample json. Its working fine. I was able to flatten.

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

  • Please don't forget to click on 130616-image.png or upvote 130671-image.png 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

image.png (128.5 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yes, indeed, I did find this as the only way to flatten out the hierarchy at both levels

However, want we went with in the end is to flatten the top level hierarchy and import the lower hierarchy as a string, we will then explode that lower hierarchy in subsequent usage where it's easier to work with

0 Votes 0 ·

@RyanAbbey-0701 - Thank you for accepting answer.

0 Votes 0 ·