How to flat complex json data into sql db using copy activity?

Question

Hi Team,

I have data with multiple arrays and inside multiple objects. I am using copy activity to load into database. I Have used collection reference to convert the data. But it's not helping much, out of 1000 users data ,either I get all users with only 'seconds viewed' information($['elements'][0]['activities']) or Only one user with other activities information(seconds views,completion,progress percentage) by changing 'collection reference'

Please provide proper cross-apply code.

Answer 1

Hi @THIMMAIAH GARI,PRASHANTH,, ,

Thanks for reaching out. Unfortunately, Copy activity is not a suitable activity for flattening nested/complex JSON array structures.

In case if your source and sink data stores are any of the below (ADF Mapping dataflow activity supported data stores), then you can you use Mapping Dataflow activity which has Flatten transformation that can be used to take arrays from complex structures and generate hierarchical-to-relational and hierarchical-to-hierarichical computed columns, aggregations, mappings.

In case if your data stores are not any of the Dataflow supported data sources, one possible way is to stage your data in one of the Data Flow supported data source and use the Flatten transformation to achieve your requirement.

Please refer to this demonstration from Mark Kromer on how to use Flatten transformation in Mapping Data flow : https://www.youtube.com/watch?v=VY2tFQJoAXE

Related info: ADF adds new hierarchical data handling and new flexibility for complex joins

In case if you have any feedback or suggestion regarding copy activity functionality, I would recommend you to please share your thoughts in ADF user voice forum. All the feedback shared in this forum are actively monitored and reviewed by ADF engineering team.

Hope this info helps. Do let us know if your have further query.

----------

Thank you
Please do consider to click on "Accept Answer" and "Upvote" on the post that helps you, as it can be beneficial to other community members.

Answer 2

If you have complex json and get the structure in dataflow, use the toString() function to get a string representation to insert into SQL.